﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Mail;
using System.Net.Mime;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Script.Serialization;
using System.Web.Services;
using System.Xml;
using WEBCienciaDeporte;
using WebRosarioFinanzas.Utils;
using Newtonsoft.Json;
using System.Web.Script.Services;

namespace WebRosarioFinanzas
{
    /// <summary>
    /// Descripción breve de Service1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // Para permitir que se llame a este servicio web desde un script, usando ASP.NET AJAX, quite la marca de comentario de la línea siguiente. 
     [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService
    {


        [WebMethod]
        public string GetArticuloPublicacion()
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();
                var sqldr = cn.EjecutarSpDataReader("spGetArticulosPublicacion");
                var dt = new DataTable();
                dt.Load(sqldr);
                var x = new ClsXml();
                x.ElementoInicial = "Articulos";
                x.Elemento = "Articulo";
                d = x.GenerarXml(dt);
                cn.Cerrar();
                return d.InnerXml;

            }
            catch (Exception ex)
            {

                return ex.Message;
            }

        }

        [WebMethod]
        public string GetArticulosPublicidadesHome()
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();
                var sqldrArt = cn.EjecutarSpDataReader("spGetArticulosHome");
                var dtArt = new DataTable();
                dtArt.Load(sqldrArt);
                cn.Cerrar();

                cn.Abrir();
                var sqldrPub = cn.EjecutarSpDataReader("spGetPublicidadHome");
                var dtPub = new DataTable();
                dtPub.Load(sqldrPub);
                cn.Cerrar();

                var dtHome = dtArt.Clone();

                int cant = 0;
                bool pubPrioritaria = false;

                if (dtPub.Rows.Count >= 1)
                {
                    if (dtPub.Rows[0]["OrdenPublicacion"].ToString() == "1")   // Se verifica si existe una publicidad prioritaria
                        pubPrioritaria = true;
                }

                foreach (DataRow dr in dtArt.Rows)                               // Artículos
                {

                    switch (cant)                                               // Publicidades
                    {

                        case 1:

                        if (dtPub.Rows.Count >= 1)
                        {
                            var drowPub = dtHome.NewRow();

                            if (pubPrioritaria)
                            {
                                if (dtPub.Rows.Count >= 2)
                                {

                                    FillRow(dtPub.Rows[0], ref drowPub);

                                    dtHome.Rows.Add(drowPub);
                                    cant++;

                                    drowPub = dtHome.NewRow();
                                    FillRow(dtPub.Rows[1], ref drowPub);
                                    dtHome.Rows.Add(drowPub);
                                    cant++;
                                }
                                else
                                {
                                    FillRow(dtPub.Rows[0], ref drowPub);
                                    dtHome.Rows.Add(drowPub);
                                    cant++;
                                }
                            }
                            else
                            {
                                FillRow(dtPub.Rows[0], ref drowPub);
                                dtHome.Rows.Add(drowPub);
                                cant++;
                            }
                        };
                        break;

                        case 6:
                        if (dtPub.Rows.Count >= 2)
                        {
                            var drowPub = dtHome.NewRow();

                            if (pubPrioritaria)
                            {
                                if (dtPub.Rows.Count >= 3)
                                {
                                    FillRow(dtPub.Rows[2], ref drowPub);
                                    dtHome.Rows.Add(drowPub);
                                    cant++;
                                }
                            }
                            else
                            {
                                FillRow(dtPub.Rows[1], ref drowPub);
                                dtHome.Rows.Add(drowPub);
                                cant++;
                            }
                        }   
                        break;

                        case 10:
                        if (dtPub.Rows.Count >= 3)
                        {
                            var drowPub = dtHome.NewRow();

                            if (pubPrioritaria)
                            {
                                if (dtPub.Rows.Count >= 4)
                                {
                                    FillRow(dtPub.Rows[3], ref drowPub);
                                    dtHome.Rows.Add(drowPub);
                                    cant++;
                                }
                            }
                            else
                            {
                                FillRow(dtPub.Rows[2], ref drowPub);
                                dtHome.Rows.Add(drowPub);
                                cant++;
                            }
                        }
                        break;
                    }

                    var drow = dtHome.NewRow();
                    FillRow(dr, ref drow);

                    dtHome.Rows.Add(drow);

                    cant++;
                }

                var x = new ClsXml();
                x.ElementoInicial = "Articulos";
                x.Elemento = "Articulo";
                d = x.GenerarXml(dtHome);
                cn.Cerrar();
                return d.InnerXml;

            }
            catch (Exception ex)
            {

                return ex.Message;
            }

        }

        public static void FillRow(DataRow original, ref DataRow destiny)
        {

            for (int i = 0; i < original.ItemArray.Count(); i++)
            {
                destiny[i] = original[i];
            }
        
        }


        [WebMethod]
        public string GetArticulo(string id)
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();
                cn.AgregarParametros("idArticulo", id);
                var sqldr = cn.EjecutarSpDataReader("spGetArticulo");
                var dt = new DataTable();
                dt.Load(sqldr);
                var x = new ClsXml();
                x.ElementoInicial = "Articulos";
                x.Elemento = "Articulo";
                d = x.GenerarXml(dt);
                cn.Cerrar();
                return d.InnerXml;

            }
            catch (Exception ex)
            {

                return ex.Message;
            }

        }

        public DataRow GetArticuloRow(string id)
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();
                cn.AgregarParametros("idArticulo", id);
                var sqldr = cn.EjecutarSpDataReader("spGetArticulo");
                var dt = new DataTable();
                dt.Load(sqldr);
                cn.Cerrar();
                return dt.Rows[0];

            }
            catch (Exception ex)
            {

                return null;
            }

        }

        [WebMethod]
        public string GetNoticia(string id)
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();
                cn.AgregarParametros("idNoticia", id);
                var sqldr = cn.EjecutarSpDataReader("spGetNoticia");
                var dt = new DataTable();
                dt.Load(sqldr);
                var x = new ClsXml();
                x.ElementoInicial = "Noticias";
                x.Elemento = "Noticia";
                d = x.GenerarXml(dt);
                cn.Cerrar();
                return d.InnerXml;

            }
            catch (Exception ex)
            {

                return ex.Message;
            }
        }

        [WebMethod]
        public List<string> GetArticulosRelacionados(string id)
        {
            try
            {

                List<string> relacionados = new List<string>();

                var d = new XmlDocument();
                var cn = new ClsConeccion();
                
                cn.Abrir();
                cn.AgregarParametros("idArticulo", id);
                var sqldr = cn.EjecutarSpDataReader("spGetArticulosSugeridos");
                var dt = new DataTable();
                dt.Load(sqldr);
                cn.Cerrar();

                if (dt.Rows.Count == 3)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        relacionados.Add("<a href=\"http://rosariofinanzas.com.ar/Articulos.aspx?id=" + dt.Rows[i]["id"].ToString() + "\" style=\" font-family: Arial,helvetica,sans-serif; font-size:14px; text-decoration: none; color: #627575;\" target=\"_blank\"> " + System.Web.HttpUtility.HtmlDecode(Regex.Replace(dt.Rows[i]["Titulo"].ToString(), "<.*?>", string.Empty).Replace("\n", "").Replace("\r", "").Replace("\t", "")) + " </a>");
                                         
                    }                                
                }

                return relacionados;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }




        public DataRow GetNoticiaRow(string id)
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();
                cn.AgregarParametros("idNoticia", id);
                var sqldr = cn.EjecutarSpDataReader("spGetNoticia");
                var dt = new DataTable();
                dt.Load(sqldr);
                cn.Cerrar();
                return dt.Rows[0];
            }
            catch (Exception ex)
            {
                return null;
            }
        }


        [WebMethod]
        public string GetArticulos()
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();
                var sqldr = cn.EjecutarSpDataReader("spGetArticulos");
                var dt = new DataTable();
                dt.Load(sqldr);
                var x = new ClsXml();
                x.ElementoInicial = "Articulos";
                x.Elemento = "Articulo";
                d = x.GenerarXml(dt);
                cn.Cerrar();
                return d.InnerXml;

            }
            catch (Exception ex)
            {

                return ex.Message;
            }

        }
        [WebMethod]
        public string GetPagina(string id)
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();
                cn.AgregarParametros("idPagina", id);
                var sqldr = cn.EjecutarSpDataReader("spGetPagina");
                var dt = new DataTable();
                dt.Load(sqldr);
                var x = new ClsXml();
                x.ElementoInicial = "Articulos";
                x.Elemento = "Articulo";
                d = x.GenerarXml(dt);
                cn.Cerrar();
                return d.InnerXml;

            }
            catch (Exception ex)
            {

                return ex.Message;
            }

        }
        [WebMethod]
        public string GetImagen(string id)
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();
                cn.AgregarParametros("idImagen", Convert.ToInt32(id));
                var sqldr = cn.EjecutarSpDataReader("spGetImagen");
                var dt = new DataTable();
                dt.Load(sqldr);
                var x = new ClsXml();
                x.ElementoInicial = "Imagenes";
                x.Elemento = "Imagen";
                d = x.GenerarXml(dt);
                cn.Cerrar();
                return d.InnerXml;

            }
            catch (Exception ex)
            {

                return ex.Message;
            }

        }
        [WebMethod]
        public string GetImagenes()
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();

                var sqldr = cn.EjecutarSpDataReader("spImagenesDatos");
                var dt = new DataTable();
                dt.Load(sqldr);
                var x = new ClsXml();
                x.ElementoInicial = "Imagenes";
                x.Elemento = "Imagen";
                d = x.GenerarXml(dt);
                cn.Cerrar();
                return d.InnerXml;

            }
            catch (Exception ex)
            {

                return ex.Message;
            }

        }
        [WebMethod]
        public string Cotizaciones()
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();

                var sqldr = cn.EjecutarSpDataReader("sp_ADM_COTIZACIONES");
                var dt = new DataTable();
                dt.Load(sqldr);
                var x = new ClsXml();
                x.ElementoInicial = "Cotizaciones";
                x.Elemento = "Cotizacion";
                d = x.GenerarXml(dt);
                cn.Cerrar();
                return d.InnerXml;

            }
            catch (Exception ex)
            {

                return ex.Message;
            }

        }
        [WebMethod]
        public string GetObjetosPaginas(string URL)
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();
                cn.AgregarParametros("URL", URL);
                var sqldr = cn.EjecutarSpDataReader("spGetObjetosPaginas");
                var dt = new DataTable();
                dt.Load(sqldr);
                var x = new ClsXml();
                x.ElementoInicial = "Objetos";
                x.Elemento = "Objeto";
                d = x.GenerarXml(dt);
                cn.Cerrar();
                return d.InnerXml;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

        }
        [WebMethod]
        public string GetMonedasActivas()
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();
                var sqldr = cn.EjecutarSpDataReader("spGetMonedasActivas");
                var dt = new DataTable();
                dt.Load(sqldr);
                var x = new ClsXml();
                x.ElementoInicial = "Monedas";
                x.Elemento = "Moneda";
                d = x.GenerarXml(dt);
                cn.Cerrar();
                return d.InnerXml;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

        }
        [WebMethod]
        public string CotizacionesMoneda(string idMoneda)
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();
                cn.AgregarParametros("intMoneda", Convert.ToInt32(idMoneda));
                var sqldr = cn.EjecutarSpDataReader("sp_ADM_COTIZACIONESMONEDA");
                var dt = new DataTable();
                dt.Load(sqldr);
                var x = new ClsXml();
                x.ElementoInicial = "Cotizaciones";
                x.Elemento = "Cotizacion";
                d = x.GenerarXml(dt);
                cn.Cerrar();
                return d.InnerXml;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

        }
        [WebMethod]
        public string GetMoneda(string idMoneda)
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();
                cn.AgregarParametros("idMoneda", Convert.ToInt32(idMoneda));
                var sqldr = cn.EjecutarSpDataReader("sp_ADM_GetMoneda");
                var dt = new DataTable();
                dt.Load(sqldr);
                var x = new ClsXml();
                x.ElementoInicial = "Monedas";
                x.Elemento = "Moneda";
                d = x.GenerarXml(dt);
                cn.Cerrar();
                return d.InnerXml;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

        }
        [WebMethod]
        public string GetTemasSubTemas()
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();
                var sqldr = cn.EjecutarSpDataReader("sp_TemasSubtemas");
                var dt = new DataTable();
                dt.Load(sqldr);
                var x = new ClsXml();
                x.ElementoInicial = "Menus";
                x.Elemento = "Menu";
                d = x.GenerarXml(dt);
                cn.Cerrar();
                return d.InnerXml;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

        }
        [WebMethod]
        public string GetSubtemas(string Temas)
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();
                cn.AgregarParametros("Seccion", Temas);
                var sqldr = cn.EjecutarSpDataReader("sp_GetSubtemas");
                var dt = new DataTable();
                dt.Load(sqldr);
                var x = new ClsXml();
                x.ElementoInicial = "Menus";
                x.Elemento = "Menu";
                d = x.GenerarXml(dt);
                cn.Cerrar();
                return d.InnerXml;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

        }

        [WebMethod]
        public void GrabarClickPublicitario(string idElemento, string URI)
        {
            try
            {                
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                
                cn.Abrir();
                cn.AgregarParametros("IdElement", idElemento);
                cn.AgregarParametros("IdElementEncripted", RijndaelManagedEncryption.EncryptRijndael(idElemento).Replace("=","" ));
                cn.AgregarParametros("Uri", URI);
                cn.AgregarParametros("FechaHora", DateTime.Now);

                var sqldr = cn.EjecutarSpDataReader("sp_ADM_NewClickAdvertising");

                cn.Cerrar();                

            }
            catch (Exception ex)
            {
                
            }

        }


        [WebMethod]
        public void InscripcionNewsletter(string email)
        {
            try
            {

                // Email a Info@Rosariofinanzas.com.ar

                var correo = new System.Net.Mail.MailMessage
                {
                    From = new System.Net.Mail.MailAddress("info@rosariofinanzas.com.ar")
                };

                correo.To.Add("info@rosariofinanzas.com.ar");
                correo.Subject = "Inscripción al Newsletter de " + email.ToLower().Trim();

                string Body = " <strong> Email de quien solicitó la inscripción: </strong> " + email.ToLower().Trim();

                AlternateView htmlView =
                AlternateView.CreateAlternateViewFromString(Body,
                                        Encoding.UTF8,
                                        MediaTypeNames.Text.Html);

                correo.AlternateViews.Add(htmlView);

                correo.Priority = System.Net.Mail.MailPriority.Normal;

                //var smtp = new System.Net.Mail.SmtpClient
                //{
                //    EnableSsl = true,
                //    Host = "smtp.gmail.com",
                //    Port = 587,
                //    Credentials = new System.Net.NetworkCredential("rf2.rosariofinanzas@gmail.com", "rosfin2011")
                //};

                var smtp = new System.Net.Mail.SmtpClient
                {
                    Host = "localhost"
                };

                smtp.Send(correo);


     // Email a la persona que se inscribió

                var correo_inscripto = new System.Net.Mail.MailMessage
                {
                    From = new System.Net.Mail.MailAddress("info@rosariofinanzas.com.ar")
                };

                correo_inscripto.To.Add(email.ToLower().Trim());
                correo_inscripto.Subject = "Inscripción al Newsletter de Rosario Finanzas";

                string Body_inscripto = GetHtmlEmailInscripcionNewsletter();

                AlternateView htmlView_inscripto =
                AlternateView.CreateAlternateViewFromString(Body_inscripto,
                                        Encoding.UTF8,
                                        MediaTypeNames.Text.Html);

                correo_inscripto.AlternateViews.Add(htmlView_inscripto);

                correo_inscripto.Priority = System.Net.Mail.MailPriority.Normal;

                //var smtp_inscripto = new System.Net.Mail.SmtpClient
                //{
                //    EnableSsl = true,
                //    Host = "smtp.gmail.com",
                //    Port = 587,
                //    Credentials = new System.Net.NetworkCredential("rf2.rosariofinanzas@gmail.com", "rosfin2011")
                //};

                var smtp_inscripto = new System.Net.Mail.SmtpClient
                {
                    Host = "localhost"
                };

                smtp_inscripto.Send(correo_inscripto);


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }



        [WebMethod]
        public void EnviarConsultaExpo(string nombre, string email, string consulta)
        {
            try
            {

                var correo = new System.Net.Mail.MailMessage
                {
                    From = new System.Net.Mail.MailAddress("info@rosariofinanzas.com.ar")
                };

                correo.To.Add("eir2015@rosariofinanzas.com.ar");
                correo.Subject = "Consulta desde EIR 2015 de " + nombre;

                string Body = " <strong> Email de quien realizó la consulta:</strong> " + email.ToLower().Trim() + "<br /> <strong>Consulta: </strong>" + consulta;

                AlternateView htmlView =
                AlternateView.CreateAlternateViewFromString(Body,
                                        Encoding.UTF8,
                                        MediaTypeNames.Text.Html);

                correo.AlternateViews.Add(htmlView);

                correo.Priority = System.Net.Mail.MailPriority.Normal;

                //var smtp = new System.Net.Mail.SmtpClient
                //{
                //    EnableSsl = true,
                //    Host = "smtp.gmail.com",
                //    Port = 587,
                //    Credentials = new System.Net.NetworkCredential("rf.rosariofinanzas@gmail.com", "rosfin2011")
                //};

                var smtp = new System.Net.Mail.SmtpClient
                {
                    Host = "localhost"
                };

                smtp.Send(correo);

            }
            catch (Exception ex)
            {

                throw ex;

            }
        }

        [WebMethod]
        public void EnviarConsultaFinanciaPyme(string nombre, string email, string consulta)
        {
            try
            {

                var correo = new System.Net.Mail.MailMessage
                {
                    From = new System.Net.Mail.MailAddress("info@rosariofinanzas.com.ar")
                };

                correo.To.Add("info@rosariofinanzas.com.ar");
                correo.Subject = "Consulta referida a FinanciaPyme 2015 de " + nombre;

                string Body = " <strong> Email de quien realizó la consulta:</strong> " + email.ToLower().Trim() + "<br /> <strong>Consulta: </strong>" + consulta;

                AlternateView htmlView =
                AlternateView.CreateAlternateViewFromString(Body,
                                        Encoding.UTF8,
                                        MediaTypeNames.Text.Html);

                correo.AlternateViews.Add(htmlView);

                correo.Priority = System.Net.Mail.MailPriority.Normal;

                //var smtp = new System.Net.Mail.SmtpClient
                //{
                //    EnableSsl = true,
                //    Host = "smtp.gmail.com",
                //    Port = 587,
                //    Credentials = new System.Net.NetworkCredential("rf.rosariofinanzas@gmail.com", "ROSFINS2011")
                //};

                var smtp = new System.Net.Mail.SmtpClient
                {
                    Host = "localhost"
                };

                smtp.Send(correo);

            }
            catch (Exception ex)
            {

                throw ex;

            }
        }


        [WebMethod]
        public string InscripcionExpo(string nombre, string apellido, string dni, string email, string telefono, string empresa)
        {
            try
            {
                string _rta = "0";

                // Inserta el registro de la Inscripcion
                var cn = new ClsConeccion();
                cn.Abrir();

                cn.AgregarParametros("Nombre", nombre);
                cn.AgregarParametros("Apellido", apellido);
                cn.AgregarParametros("DNI", dni.Trim());
                cn.AgregarParametros("Email", email.Trim());
                cn.AgregarParametros("Telefono", telefono);
                cn.AgregarParametros("Profesion", empresa);

                cn.AgregarParametros("ProfesionDescripcion", "");
                cn.AgregarParametros("Extra", "");
                cn.AgregarParametros("Invitado", "");
                cn.AgregarParametros("Publicidad", "");
                cn.AgregarParametros("Comentarios", "");
                cn.AgregarParametros("Evento", "EIR2015");
                cn.AgregarParametros("MedioPago", "Deposito");
                cn.AgregarParametros("EstadoPago", "Pendiente");
                
                var rd = cn.EjecutarSpDataReader("sp_ADM_NewPersonaEXPO");

                while (rd.Read())
                {
                    _rta = Convert.ToString(rd[0]);
                }
                cn.Cerrar();
          
                // Envía email de confirmacion al inscripto
                var correo = new System.Net.Mail.MailMessage
                {
                    From = new System.Net.Mail.MailAddress("eir2015@rosariofinanzas.com.ar")
                };

                correo.To.Add(email.Trim());
                correo.Subject = "EIR 2015: Inscripción satisfactoria";

                string Body = GetHtmlEmailEXPO(nombre);

                AlternateView htmlView =
                AlternateView.CreateAlternateViewFromString(Body,
                                        Encoding.UTF8,
                                        MediaTypeNames.Text.Html);

                correo.AlternateViews.Add(htmlView);

                correo.Priority = System.Net.Mail.MailPriority.Normal;

                //var smtp = new System.Net.Mail.SmtpClient
                //{
                //    EnableSsl = true,
                //    Host = "smtp.gmail.com",
                //    Port = 587,
                //    Credentials = new System.Net.NetworkCredential("rf.rosariofinanzas@gmail.com", "ROSFIN2011")
                //};

                var smtp = new System.Net.Mail.SmtpClient
                {
                    Host = "localhost"
                };

                smtp.Send(correo);

                return _rta;
            }
            catch (Exception ex)
            {

                throw ex;

            }
        }


        [WebMethod]
        public string InscripcionFinanciaPyme(string nombre, string apellido, string dni, string email, string telefono, string empresa, string urlReferencia)
        {
            try
            {
                string _rta = "0";

                // Inserta el registro de la Inscripcion
                var cn = new ClsConeccion();
                cn.Abrir();

                cn.AgregarParametros("Nombre", nombre);
                cn.AgregarParametros("Apellido", apellido);
                cn.AgregarParametros("DNI", dni.Trim());
                cn.AgregarParametros("Email", email.Trim());
                cn.AgregarParametros("Telefono", telefono);
                cn.AgregarParametros("Profesion", empresa);

                cn.AgregarParametros("ProfesionDescripcion", "");
                cn.AgregarParametros("Extra", "");
                cn.AgregarParametros("Invitado", "");
                cn.AgregarParametros("Publicidad", "");
                cn.AgregarParametros("Comentarios", "");
                cn.AgregarParametros("Evento", "FinanciaPyme2015_Venado");
                cn.AgregarParametros("MedioPago", "Deposito");
                cn.AgregarParametros("EstadoPago", "Pendiente");
                cn.AgregarParametros("UrlDeReferencia", urlReferencia);


                var rd = cn.EjecutarSpDataReader("sp_ADM_NewPersonaEXPO");

                while (rd.Read())
                {
                    _rta = Convert.ToString(rd[0]);
                }
                cn.Cerrar();

                // Envía email de confirmacion al inscripto
                var correo = new System.Net.Mail.MailMessage
                {
                    From = new System.Net.Mail.MailAddress("info@rosariofinanzas.com.ar")
                };

                correo.To.Add(email.Trim());
                correo.Subject = "FinanciaPyme 2015: Inscripción satisfactoria";

                string Body = GetHtmlEmailFinanciaPyme(nombre);

                AlternateView htmlView =
                AlternateView.CreateAlternateViewFromString(Body,
                                        Encoding.UTF8,
                                        MediaTypeNames.Text.Html);

                correo.AlternateViews.Add(htmlView);

                correo.Priority = System.Net.Mail.MailPriority.Normal;

                //var smtp = new System.Net.Mail.SmtpClient
                //{
                //    EnableSsl = true,
                //    Host = "smtp.gmail.com",
                //    Port = 587,
                //    Credentials = new System.Net.NetworkCredential("rf.rosariofinanzas@gmail.com", "ROSFIN2011")
                //};

                var smtp = new System.Net.Mail.SmtpClient
                {
                    Host = "localhost"
                };

                smtp.Send(correo);

                return _rta;
            }
            catch (Exception ex)
            {

                throw ex;

            }
        }




        [WebMethod]
        public string GetNroWeb(string nombre, string apellido, string dni, string provincia, string email)
        {

            string rta = string.Empty;

            Int32 idSorteo = 0;
            string nombreSorteo = string.Empty;
            string fechaSorteo = string.Empty;

            var cn = new ClsConeccion();
            cn.Abrir();

            var rd0 = cn.EjecutarSpDataReader("sp_GetSorteo");

            while (rd0.Read())
            {
                idSorteo = Convert.ToInt32(rd0["idSorteo"]);
                nombreSorteo = rd0["Sorteo"].ToString();
                fechaSorteo = rd0["FechaSorteo"].ToString();
            }
            rd0.Close();

            cn.AgregarParametros("Nombre", nombre.Trim());
            cn.AgregarParametros("Apellido", apellido.Trim());
            cn.AgregarParametros("Provincia", provincia.Trim());
            cn.AgregarParametros("Mail", email.ToLower().Trim());
            cn.AgregarParametros("TipoIngreso", 1);
            cn.AgregarParametros("IdSorteo", idSorteo);
            cn.AgregarParametros("DNI", dni);

            var rd = cn.EjecutarSpDataReader("sp_GrabarParticipante");
            while (rd.Read())
            {

                if (rd["nro"].ToString() == "-1")
                {
                    rta = "-1";
                }
                else
                {
                    var correo = new System.Net.Mail.MailMessage
                    {
                        From = new System.Net.Mail.MailAddress("info@rosariofinanzas.com.ar")
                    };
                    correo.To.Add(email.ToLower().Trim());
                    correo.Subject = "Sorteo Rosario Finanzas - " + nombreSorteo;

                    string Body = GetHtmlEmailSorteo(nombreSorteo, Convert.ToDateTime(fechaSorteo).ToShortDateString(), rd["nro"].ToString());

                    List<string> list = new List<string>();

                    int c = 1;
                    while (Body.IndexOf("<img alt=\"\" src=\"http") > 0)
                    {
                        string aux = Body.Substring(Body.IndexOf("<img alt=\"\" src=\"http") + 17);
                        string aux2 = Body.Substring(Body.IndexOf("<img alt=\"\" src=\"http") + 17, aux.IndexOf("\" />"));
                        if (aux2.IndexOf("style") > 0)
                            aux2 = aux2.Substring(0, aux2.IndexOf("style") - 2);
                        list.Add(aux2);
                        Body = Body.Replace(aux2, "cid:Imagen" + c.ToString());
                        c++;
                    }

                    AlternateView htmlView =
                        AlternateView.CreateAlternateViewFromString(Body,
                                                Encoding.UTF8,
                                                MediaTypeNames.Text.Html);

                    c = 1;
                    foreach (string link in list)
                    {
                        LinkedResource img = null;
                        if (LinkExtension(link) == "Gif")
                            img = new LinkedResource(Link(link), MediaTypeNames.Image.Gif);
                        if (LinkExtension(link) == "jpg")
                            img = new LinkedResource(Link(link), MediaTypeNames.Image.Jpeg);
                        if (LinkExtension(link) != "jpg" && LinkExtension(link) != "Gif")
                            img = new LinkedResource(Link(link), "image/png");
                        img.ContentId = "Imagen" + c.ToString();
                        htmlView.LinkedResources.Add(img);
                        c++;
                    }

                    correo.AlternateViews.Add(htmlView);

                    correo.Priority = System.Net.Mail.MailPriority.Normal;

                    //var smtp = new System.Net.Mail.SmtpClient
                    //{
                    //    EnableSsl = true,
                    //    Host = "smtp.gmail.com",
                    //    Port = 587,
                    //    Credentials = new System.Net.NetworkCredential("rf.rosariofinanzas@gmail.com", "rosfin2011")
                    //};

                    var smtp = new System.Net.Mail.SmtpClient
                    {
                        Host = "localhost"
                    };

                    smtp.Send(correo);

                    rta = rd["nro"].ToString(); 

                }
            }

            rd.Close();
            cn.Cerrar();

            return rta;
        }

        [WebMethod]
        public DataTable GetNewsLetterPendientes()
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();

                var sqldr = cn.EjecutarSpDataReader("sp_GetNewsLetterPendientes");
                var dt = new DataTable();
                dt.Load(sqldr);

                cn.Cerrar();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        [WebMethod]
        public void UpdateEstadoNewsLetter(int id)
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();

                cn.AgregarParametros("id", id);
                var sqldr = cn.EjecutarSpDataReader("sp_UpdateEstadoNewsLetter");              

                cn.Cerrar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        [WebMethod]
        public string GetCursosVigentes()
        {
            try
            {
                var d = new XmlDocument();
                var cn = new ClsConeccion();
                cn.Abrir();

                var sqldr = cn.EjecutarSpDataReader("sp_GetCursosVigentes");                              

                var dt = new DataTable();
                dt.Load(sqldr);

                foreach (DataRow dr in dt.Rows)                       
                {
                    dr["Importe"] = Math.Round(Convert.ToDecimal( dr["Importe"]), 2).ToString();
                }

                var x = new ClsXml();
                x.ElementoInicial = "Cursos";
                x.Elemento = "Curso";
                d = x.GenerarXml(dt);
                cn.Cerrar();

                return d.InnerXml;
               
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        [WebMethod]
        public string GetNroRedesSociales(string dni, string tipo)
        {
            try
            {
                string rta = string.Empty;

                Int32 idSorteo = 0;
                string nombreSorteo = string.Empty;
                string fechaSorteo = string.Empty;
                string email = string.Empty;

                var cn = new ClsConeccion();
                cn.Abrir();

                var rd = cn.EjecutarSpDataReader("sp_GetSorteo");
                
                while (rd.Read())
                {
                    idSorteo = Convert.ToInt32(rd["idSorteo"]);
                    nombreSorteo = rd["Sorteo"].ToString();
                    fechaSorteo = rd["FechaSorteo"].ToString();
                }
                rd.Close();

                cn.AgregarParametros("IdSorteo", idSorteo);
                cn.AgregarParametros("DNI", dni.Trim());
                var rd0 = cn.EjecutarSpDataReader("sp_GetParticipante_ByDNIandIdSorteo");
                
                while (rd0.Read())
                {
                    email = rd0["Email"].ToString();
                }
                rd0.Close();

                cn.LimpiarParametros();

                cn.AgregarParametros("DNI", dni.Trim());
                cn.AgregarParametros("TipoIngreso", tipo);
                cn.AgregarParametros("IdSorteo", idSorteo);

                var rd2 = cn.EjecutarSpDataReader("sp_GrabarParticipante");
                while (rd2.Read())
                {
                    if (rd2["nro"].ToString() == "-2" || rd2["nro"].ToString() == "-3")
                    {
                        rta = "-1";
                    }
                    else
                    {

                        var correo = new System.Net.Mail.MailMessage
                        {
                            From = new System.Net.Mail.MailAddress("info@rosariofinanzas.com.ar")
                        };
                        correo.To.Add(email.ToLower().Trim());
                        correo.Subject = "Sorteo Rosario Finanzas - " + nombreSorteo;

                        string Body = GetHtmlEmailSorteoRedesSociales(nombreSorteo, Convert.ToDateTime(fechaSorteo).ToShortDateString(), rd2["nro"].ToString(), tipo == "2" ? "Facebook" : "Twitter");

                        List<string> list = new List<string>();

                        int c = 1;
                        while (Body.IndexOf("<img alt=\"\" src=\"http") > 0)
                        {
                            string aux = Body.Substring(Body.IndexOf("<img alt=\"\" src=\"http") + 17);
                            string aux2 = Body.Substring(Body.IndexOf("<img alt=\"\" src=\"http") + 17, aux.IndexOf("\" />"));
                            if (aux2.IndexOf("style") > 0)
                                aux2 = aux2.Substring(0, aux2.IndexOf("style") - 2);
                            list.Add(aux2);
                            Body = Body.Replace(aux2, "cid:Imagen" + c.ToString());
                            c++;
                        }

                        AlternateView htmlView =
                            AlternateView.CreateAlternateViewFromString(Body,
                                                    Encoding.UTF8,
                                                    MediaTypeNames.Text.Html);

                        c = 1;
                        foreach (string link in list)
                        {
                            LinkedResource img = null;
                            if (LinkExtension(link) == "Gif")
                                img = new LinkedResource(Link(link), MediaTypeNames.Image.Gif);
                            if (LinkExtension(link) == "jpg")
                                img = new LinkedResource(Link(link), MediaTypeNames.Image.Jpeg);
                            if (LinkExtension(link) != "jpg" && LinkExtension(link) != "Gif")
                                img = new LinkedResource(Link(link), "image/png");
                            img.ContentId = "Imagen" + c.ToString();
                            htmlView.LinkedResources.Add(img);
                            c++;
                        }

                        correo.AlternateViews.Add(htmlView);

                        correo.Priority = System.Net.Mail.MailPriority.Normal;

                        //var smtp = new System.Net.Mail.SmtpClient
                        //{
                        //    EnableSsl = true,
                        //    Host = "smtp.gmail.com",
                        //    Port = 587,
                        //    Credentials = new System.Net.NetworkCredential("rf.rosariofinanzas@gmail.com", "rosfin2011")
                        //};

                        var smtp = new System.Net.Mail.SmtpClient
                        {
                            Host = "localhost"
                        };

                        smtp.Send(correo);

                        rta = rd2["nro"].ToString();
                    }
                }

                rd2.Close();

                cn.Cerrar();
                return rta;

            }
            catch (Exception ex)
            {
                return ex.Message;
            }

        }

        [WebMethod]
        public void EnviarEmailSorteo(string email, string nro, string tipo)
        {
            
            Int32 idSorteo = 0;
            string nombreSorteo = string.Empty;
            string fechaSorteo = string.Empty;

            var cn = new ClsConeccion();
            cn.Abrir();

            var rd = cn.EjecutarSpDataReader("sp_GetSorteo");

            while (rd.Read())
            {
                idSorteo = Convert.ToInt32(rd["idSorteo"]);
                nombreSorteo = rd["Sorteo"].ToString();
                fechaSorteo = rd["FechaSorteo"].ToString();
            }
            rd.Close();

            var correo = new System.Net.Mail.MailMessage
            {
                From = new System.Net.Mail.MailAddress("info@rosariofinanzas.com.ar")
            };
            correo.To.Add(email.ToLower().Trim());
            correo.Subject = "Sorteo Rosario Finanzas - " + nombreSorteo;

            string Body = GetHtmlEmailSorteoRedesSociales(nombreSorteo, Convert.ToDateTime(fechaSorteo).ToShortDateString(), nro, tipo == "2" ? "Facebook" : "Twitter");

            List<string> list = new List<string>();

            int c = 1;
            while (Body.IndexOf("<img alt=\"\" src=\"http") > 0)
            {
                string aux = Body.Substring(Body.IndexOf("<img alt=\"\" src=\"http") + 17);
                string aux2 = Body.Substring(Body.IndexOf("<img alt=\"\" src=\"http") + 17, aux.IndexOf("\" />"));
                if (aux2.IndexOf("style") > 0)
                    aux2 = aux2.Substring(0, aux2.IndexOf("style") - 2);
                list.Add(aux2);
                Body = Body.Replace(aux2, "cid:Imagen" + c.ToString());
                c++;
            }

            AlternateView htmlView =
                AlternateView.CreateAlternateViewFromString(Body,
                                        Encoding.UTF8,
                                        MediaTypeNames.Text.Html);

            c = 1;
            foreach (string link in list)
            {
                LinkedResource img = null;
                if (LinkExtension(link) == "Gif")
                    img = new LinkedResource(Link(link), MediaTypeNames.Image.Gif);
                if (LinkExtension(link) == "jpg")
                    img = new LinkedResource(Link(link), MediaTypeNames.Image.Jpeg);
                if (LinkExtension(link) != "jpg" && LinkExtension(link) != "Gif")
                    img = new LinkedResource(Link(link), "image/png");
                img.ContentId = "Imagen" + c.ToString();
                htmlView.LinkedResources.Add(img);
                c++;
            }

            correo.AlternateViews.Add(htmlView);

            correo.Priority = System.Net.Mail.MailPriority.Normal;

            //var smtp = new System.Net.Mail.SmtpClient
            //{
            //    EnableSsl = true,
            //    Host = "smtp.gmail.com",
            //    Port = 587,
            //    Credentials = new System.Net.NetworkCredential("rf.rosariofinanzas@gmail.com", "rosfin2011")
            //};

            var smtp = new System.Net.Mail.SmtpClient
            {
                Host = "localhost"
            };

            smtp.Send(correo);
        }

        private string GetHtmlEmailInscripcionNewsletter()
        {

            string html = "";

            // CABECERA
            html += "<div style=\"padding: 30px; height: auto; background-color: rgb(243, 238, 225);\">";
            html += "<div style=\"text-align: center;\">";
            html += "</div>";
            html += "<div style=\"text-align: center; background-color: white;\">";
            html += "<div style=\"width: 100%; height: 20px; background-color: rgb(141, 105, 13);\">";
            html += "</div>";
            html += "<table style=\"width: 96%; height: 120px; text-align: left; margin-top: 10px; margin-right: 2%; margin-left: 2%; border-bottom-color: rgb(141, 105, 13); border-bottom-width: 3px; border-bottom-style: double; background-color: white;\">";
            html += "<tbody>";
            html += "<tr>";
            html += "<td>";
            html += " <img alt=\"Logo Rosario Finanzas\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/RosarioNews.jpg\" style=\"width: 174px; height: 44px;\" /></td>";
            html += "<td style=\"text-align: right;\">";
            html += "<span style=\"font-family: arial,helvetica,sans-serif; font-size: 24px;\"><strong>Inscripción a Newsletter</strong></span><br />";
            html += "<br />";
            html += "<span style=\"color: black; font-family: arial,helvetica,sans-serif; font-size: 14px;\"><a href=\"http://rosariofinanzas.com.ar\" style=\"text-decoration: none;\">www.rosariofinanzas.com.ar</a></span></td>";
            html += "</tr>";
            html += "</tbody>";
            html += "</table>";


            // CUERPO
            html += "<p>";
            html += "Usted se ha inscrito a nuestro newsletter";
            html += "</p>";


            html += "<p>";
            html += "¡Muchas gracias por elegirnos!";
            html += "</p>";


            html += "<p>";
            html += "Visite nuestra sección de cursos si desea aprender a manejar sus finanzas personales: <br/> http://rosariofinanzas.com.ar/cursos/cursos.html";
            html += "</p>";


            // PIE
            html += "<div style=\"padding: 10px; height: 25px; text-align: right; background-color: rgb(141, 105, 13);\">";
            html += "<a href=\"https://www.facebook.com/rosario.finanzas\" target=\"_blank\"><img alt=\"Facebook\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/face.png\" style=\"border-width: 0px; border-style: solid; width: 23px; height: 22px;\" /></a> <a href=\"https://twitter.com/rosariofinanzas\" target=\"_blank\"> <img alt=\"Twitter\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/twit.png\" style=\"border-width: 0px; border-style: solid; width: 23px; height: 22px;\" /></a></div>";
            html += "</div>";
            html += "</div>";
            html += "<p>";
            html += "</p>";

            return html;
        }

        private string GetHtmlEmailSorteo(string nombreSorteo, string fecha, string nro)
        {

            string html = "";

            // CABECERA
            html += "<div style=\"padding: 30px; height: auto; background-color: rgb(243, 238, 225);\">";
            html += "<div style=\"text-align: center;\">";
            html += "</div>";
            html += "<div style=\"text-align: center; background-color: white;\">";
            html += "<div style=\"width: 100%; height: 20px; background-color: rgb(141, 105, 13);\">";
            html += "</div>";
            html += "<table style=\"width: 96%; height: 120px; text-align: left; margin-top: 10px; margin-right: 2%; margin-left: 2%; border-bottom-color: rgb(141, 105, 13); border-bottom-width: 3px; border-bottom-style: double; background-color: white;\">";
            html += "<tbody>";
            html += "<tr>";
            html += "<td>";
            html += " <img alt=\"Logo Rosario Finanzas\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/RosarioNews.jpg\" style=\"width: 174px; height: 44px;\" /></td>";
            html += "<td style=\"text-align: right;\">";
            html += "<span style=\"font-family: arial,helvetica,sans-serif; font-size: 24px;\"><strong>Sorteos</strong></span><br />";
            html += "<br />";
            html += "<span style=\"color: black; font-family: arial,helvetica,sans-serif; font-size: 14px;\"><a href=\"http://rosariofinanzas.com.ar\" style=\"text-decoration: none;\">www.rosariofinanzas.com.ar</a></span></td>";
            html += "</tr>";
            html += "</tbody>";
            html += "</table>";


            // CUERPO
            html += "<p>";
            html += "Usted está participando en el sorteo '<strong>" + nombreSorteo + "'</strong> que se realizará el día <strong>" + fecha + "</strong>";
            html += "</p>";


            html += "<p>";
            html += "Su número es: <strong>" + nro + "</strong>";
            html += "</p>";


            // PIE
            html += "<p>";
            html += "Lea atentamente las <a href=\"http://rosariofinanzas.com.ar/Sorteo/BaseyCondiciones.aspx\" target=\"_blank\" style=\"text-decoration: none;\">Bases y Condiciones</a>";
            html += "</p>";
            html += "<p>";
            html += "<strong> Visite nuestra sección de cursos si desea aprender a manejar sus finanzas personales: </strong> <br/> http://rosariofinanzas.com.ar/cursos/cursos.html";
            html += "</p>";

            html += "<div style=\"padding: 10px; height: 25px; text-align: right; background-color: rgb(141, 105, 13);\">";
            html += "<a href=\"https://www.facebook.com/rosario.finanzas\" target=\"_blank\"><img alt=\"Facebook\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/face.png\" style=\"border-width: 0px; border-style: solid; width: 23px; height: 22px;\" /></a> <a href=\"https://twitter.com/rosariofinanzas\" target=\"_blank\"> <img alt=\"Twitter\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/twit.png\" style=\"border-width: 0px; border-style: solid; width: 23px; height: 22px;\" /></a></div>";
            html += "</div>";
            html += "</div>";
            html += "<p>";
            html += "</p>";

            return html;
        }

        private string GetHtmlEmailSorteoRedesSociales(string nombreSorteo, string fecha, string nro, string redSocial)
        {

            string html = "";

            // CABECERA
            html += "<div style=\"padding: 30px; height: auto; background-color: rgb(243, 238, 225);\">";
            html += "<div style=\"text-align: center;\">";
            html += "</div>";
            html += "<div style=\"text-align: center; background-color: white;\">";
            html += "<div style=\"width: 100%; height: 20px; background-color: rgb(141, 105, 13);\">";
            html += "</div>";
            html += "<table style=\"width: 96%; height: 120px; text-align: left; margin-top: 10px; margin-right: 2%; margin-left: 2%; border-bottom-color: rgb(141, 105, 13); border-bottom-width: 3px; border-bottom-style: double; background-color: white;\">";
            html += "<tbody>";
            html += "<tr>";
            html += "<td>";
            html += " <img alt=\"Logo Rosario Finanzas\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/RosarioNews.jpg\" style=\"width: 174px; height: 44px;\" /></td>";
            html += "<td style=\"text-align: right;\">";
            html += "<span style=\"font-family: arial,helvetica,sans-serif; font-size: 24px;\"><strong>Sorteos</strong></span><br />";
            html += "<br />";
            html += "<span style=\"color: black; font-family: arial,helvetica,sans-serif; font-size: 14px;\"><a href=\"http://rosariofinanzas.com.ar\" style=\"text-decoration: none;\">www.rosariofinanzas.com.ar</a></span></td>";
            html += "</tr>";
            html += "</tbody>";
            html += "</table>";


            // CUERPO
            html += "<p>";
            html += "Usted está participando en el sorteo '<strong>" + nombreSorteo + "'</strong> que se realizará el día <strong>" + fecha + "</strong>";
            html += "</p>";


            html += "<p>";
            html += "Su número obtenido por <strong>" + redSocial + "</strong> es: <strong>" + nro + "</strong>";
            html += "</p>";


            // PIE
            html += "<p>";
            html += "Lea atentamente las <a href=\"http://rosariofinanzas.com.ar/Sorteo/BasesyCondiciones.aspx\" target=\"_blank\" style=\"text-decoration: none;\">Bases y Condiciones</a>";
            html += "</p>";
            html += "<p>";
            html += "<strong> Visite nuestra sección de cursos si desea aprender a manejar sus finanzas personales: </strong> <br/> http://rosariofinanzas.com.ar/cursos/cursos.html";
            html += "</p>";
            html += "<div style=\"padding: 10px; height: 25px; text-align: right; background-color: rgb(141, 105, 13);\">";
            html += "<a href=\"https://www.facebook.com/rosario.finanzas\" target=\"_blank\"><img alt=\"Facebook\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/face.png\" style=\"border-width: 0px; border-style: solid; width: 23px; height: 22px;\" /></a> <a href=\"https://twitter.com/rosariofinanzas\" target=\"_blank\"> <img alt=\"Twitter\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/twit.png\" style=\"border-width: 0px; border-style: solid; width: 23px; height: 22px;\" /></a></div>";
            html += "</div>";
            html += "</div>";
            html += "<p>";
            html += "</p>";

            return html;
        }

        private string GetHtmlEmailInscripcionCursoPago(string nombreCurso, string fecha, string lugar, string precio, string cantJornadas)
        {

            string html = "";

            // CABECERA
            html += "<div style=\"padding: 30px; height: auto; background-color: rgb(243, 238, 225);\">";
            html += "<div style=\"text-align: center;\">";
            html += "</div>";
            html += "<div style=\"text-align: center; background-color: white;\">";
            html += "<div style=\"width: 100%; height: 20px; background-color: rgb(141, 105, 13);\">";
            html += "</div>";
            html += "<table style=\"width: 96%; height: 120px; text-align: left; margin-top: 10px; margin-right: 2%; margin-left: 2%; border-bottom-color: rgb(141, 105, 13); border-bottom-width: 3px; border-bottom-style: double; background-color: white;\">";
            html += "<tbody>";
            html += "<tr>";
            html += "<td>";
            html += " <img alt=\"Logo Rosario Finanzas\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/RosarioNews.jpg\" style=\"width: 174px; height: 44px;\" /></td>";
            html += "<td style=\"text-align: right;\">";
            html += "<span style=\"font-family: arial,helvetica,sans-serif; font-size: 24px;\"><strong>Cursos</strong></span><br />";
            html += "<br />";
            html += "<span style=\"color: black; font-family: arial,helvetica,sans-serif; font-size: 14px;\"><a href=\"http://rosariofinanzas.com.ar\" style=\"text-decoration: none;\">www.rosariofinanzas.com.ar</a></span></td>";
            html += "</tr>";
            html += "</tbody>";
            html += "</table>";


            if (cantJornadas == "1")
            {
                // CUERPO
                html += "<p>";
                html += "Usted se ha inscripto satisfactoriamente al curso '<strong>" + nombreCurso + "'</strong> que se realizará el día <strong>" + fecha + "</strong> y que se dictará en <strong>" + lugar + "</strong>";
                html += "</p>";
            }
            else
            {

                // CUERPO
                html += "<p>";
                html += "Usted se ha inscripto satisfactoriamente al curso '<strong>" + nombreCurso + "'</strong> que se realizará los días <strong>" + fecha + "</strong> y que se dictará en <strong>" + lugar + "</strong>";
                html += "</p>";
            
            }

            html += "<p>";
            html += "Recuerde que para confirmar su inscripción debe abonar el costo correspondiente. Si aún no lo hizo, puede hacerlo en el siguiente botón: ";
            html += "</p>";

            html += "<a target=\"_blank\" href=\"http://rosariofinanzas.com.ar/Cursos/cursos.html\"><img class=\"Cabecera\" src=\"http://rosariofinanzas.com.ar/Imagenes/dmButton.png\" /></a>";
            html += "<p><strong>Este botón lo llevará nuevamente a nuestra web de cursos. Una vez allí, busque este mismo curso y haga click en el botón 'Pagar' (Es idéntico al botón anterior). <br/> Si el cupo del curso se completa, el botón de pagos de nuestra web dejará de estar disponible así que no olvide realizar el pago lo antes posible porque de lo contrario, perderá su lugar.</strong></p>";
            
            html += "<div style=\"padding: 10px; height: 25px; text-align: right; background-color: rgb(141, 105, 13);\">";
            html += "<a href=\"https://www.facebook.com/rosario.finanzas\" target=\"_blank\"><img alt=\"Facebook\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/face.png\" style=\"border-width: 0px; border-style: solid; width: 23px; height: 22px;\" /></a> <a href=\"https://twitter.com/rosariofinanzas\" target=\"_blank\"> <img alt=\"Twitter\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/twit.png\" style=\"border-width: 0px; border-style: solid; width: 23px; height: 22px;\" /></a></div>";
            html += "</div>";
            html += "</div>";

            return html;
        }

        private string GetHtmlEmailInscripcionCursoGratuito(string nombreCurso, string fecha, string lugar, string precio, string cantJornadas)
        {

            string html = "";

            // CABECERA
            html += "<div style=\"padding: 30px; height: auto; background-color: rgb(243, 238, 225);\">";
            html += "<div style=\"text-align: center;\">";
            html += "</div>";
            html += "<div style=\"text-align: center; background-color: white;\">";
            html += "<div style=\"width: 100%; height: 20px; background-color: rgb(141, 105, 13);\">";
            html += "</div>";
            html += "<table style=\"width: 96%; height: 120px; text-align: left; margin-top: 10px; margin-right: 2%; margin-left: 2%; border-bottom-color: rgb(141, 105, 13); border-bottom-width: 3px; border-bottom-style: double; background-color: white;\">";
            html += "<tbody>";
            html += "<tr>";
            html += "<td>";
            html += " <img alt=\"Logo Rosario Finanzas\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/RosarioNews.jpg\" style=\"width: 174px; height: 44px;\" /></td>";
            html += "<td style=\"text-align: right;\">";
            html += "<span style=\"font-family: arial,helvetica,sans-serif; font-size: 24px;\"><strong>Cursos</strong></span><br />";
            html += "<br />";
            html += "<span style=\"color: black; font-family: arial,helvetica,sans-serif; font-size: 14px;\"><a href=\"http://rosariofinanzas.com.ar\" style=\"text-decoration: none;\">www.rosariofinanzas.com.ar</a></span></td>";
            html += "</tr>";
            html += "</tbody>";
            html += "</table>";


            if (cantJornadas == "1")
            {
                // CUERPO
                html += "<p>";
                html += "Usted se ha inscripto satisfactoriamente al curso '<strong>" + nombreCurso + "'</strong> que se realizará el día <strong>" + fecha + "</strong> y que se dictará en <strong>" + lugar + "</strong>";
                html += "</p>";
            }
            else
            {
                // CUERPO
                html += "<p>";
                html += "Usted se ha inscripto satisfactoriamente al curso '<strong>" + nombreCurso + "'</strong> que se realizará los días <strong>" + fecha + "</strong> y que se dictará en <strong>" + lugar + "</strong>";
                html += "</p>";
            }

            html += "<div style=\"padding: 10px; height: 25px; text-align: right; background-color: rgb(141, 105, 13);\">";
            html += "<a href=\"https://www.facebook.com/rosario.finanzas\" target=\"_blank\"><img alt=\"Facebook\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/face.png\" style=\"border-width: 0px; border-style: solid; width: 23px; height: 22px;\" /></a> <a href=\"https://twitter.com/rosariofinanzas\" target=\"_blank\"> <img alt=\"Twitter\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/twit.png\" style=\"border-width: 0px; border-style: solid; width: 23px; height: 22px;\" /></a></div>";
            html += "</div>";
            html += "</div>";

            return html;
        }

        private string GetHtmlEmailEXPO(string nombre)
        {

            string html = "";

            // CABECERA
            html += "<div style=\"padding: 30px; height: auto; background-color: rgb(243, 238, 225);\">";
            html += "<div style=\"text-align: center;\">";
            html += "</div>";
            html += "<div style=\"text-align: center; background-color: white;\">";
            html += "<div style=\"width: 100%; height: 20px; background-color: rgb(141, 105, 13);\">";
            html += "</div>";
            html += "<table style=\"width: 96%; height: 120px; text-align: left; margin-top: 10px; margin-right: 2%; margin-left: 2%; border-bottom-color: rgb(141, 105, 13); border-bottom-width: 3px; border-bottom-style: double; background-color: white;\">";
            html += "<tbody>";
            html += "<tr>";
            html += "<td>";
            html += " <img alt=\"Logo Rosario Finanzas\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ExpoInversiones/img/logo.png\" style=\"width: 174px; height: 44px;\" /></td>";
            html += "<td style=\"text-align: right;\">";
            html += "<span style=\"font-family: arial,helvetica,sans-serif; font-size: 24px;\"><strong>Expo Inversiones 2015</strong></span><br />";
            html += "<br />";
            html += "<span style=\"color: black; font-family: arial,helvetica,sans-serif; font-size: 14px;\"><a href=\"http://rosariofinanzas.com.ar/ExpoInversiones/Expo.html\" style=\"text-decoration: none;\">Expo Inversiones 2015</a></span></td>";
            html += "</tr>";
            html += "</tbody>";
            html += "</table>";

            // CUERPO
            html += "<p>";
            html += "Muchas gracias " + nombre + " por inscribirse a <strong>Expo Inversiones Rosario 2015</strong>. Usted está habilitado/a para visitar la Expo y todos los stands sobre un importante universo de inversiones.";
            html += "</p>";


            html += "<p>";
            html += "Sin embargo, si quiere sacar el máximo provecho de nuestra jornada puede inscribirse a las charlas de reconocidas personalidades del mundo de la economía y las inversiones entre las que se cuentan Damián Tabakman, Claudio Zuchovicky y Maxi Montenegro entre otros. Para inscribirse a nuestras charlas, debe abonar el precio correspondiente haciendo click en el siguiente link:";
            html += "</p>";

            html += "<form action='https://argentina.dineromail.com/Shop/Shop_Ingreso.asp' style=\"display:initial;\" method='post' target=\"_blank\">";
            html += "<input type='hidden' name='NombreItem' value='Expo Inversiones Rosario 2015' />";
            html += "<input type='hidden' name='TipoMoneda' value='1' />";
            html += "<input type='hidden' name='PrecioItem' value='400.00' />";
            html += "<input type='hidden' name='E_Comercio' value='3433613' />";
            html += "<input type='hidden' name='NroItem' value='-' />";
            html += "<input type='hidden' name='image_url' value='http://rosariofinanzas.com.ar/Imagenes/logodinero.png' />";
            html += "<input type='hidden' name='DireccionExito' value='http://rosariofinanzas.com.ar/PagoExitoso.aspx' />";
            html += "<input type='hidden' name='DireccionFracaso' value='http://rosariofinanzas.com.ar/PagoCancelado.aspx' />";
            html += "<input type='hidden' name='DireccionEnvio' value='0' />";
            html += "<input type='hidden' name='Mensaje' value='1' />";
            html += "<input type='hidden' name='MediosPago' value='4,2,7,13' />";
            html += "<input type='image' class=\"imgDineroMail\" src='https://argentina.dineromail.com/import/img/payment-button-ar.png?dmbypayu' border='0' name='submit' alt='Pagar con DineroMail' />";
            html += "</form>";

            

            html += "<div style=\"padding: 10px; height: 25px; text-align: right; background-color: rgb(141, 105, 13);\">";
            html += "<a href=\"https://www.facebook.com/rosario.finanzas\" target=\"_blank\"><img alt=\"Facebook\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/face.png\" style=\"border-width: 0px; border-style: solid; width: 23px; height: 22px;\" /></a> <a href=\"https://twitter.com/rosariofinanzas\" target=\"_blank\"> <img alt=\"Twitter\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/twit.png\" style=\"border-width: 0px; border-style: solid; width: 23px; height: 22px;\" /></a></div>";
            html += "</div>";
            html += "</div>";
            html += "<p>";
            html += "</p>";

            return html;
        }

        private string GetHtmlEmailFinanciaPyme(string nombre)
        {

            string html = "";

            // CABECERA
            html += "<div style=\"padding: 30px; height: auto; background-color: rgb(243, 238, 225);\">";
            html += "<div style=\"text-align: center;\">";
            html += "</div>";
            html += "<div style=\"text-align: center; background-color: white;\">";
            html += "<div style=\"width: 100%; height: 20px; background-color: rgb(141, 105, 13);\">";
            html += "</div>";
            html += "<table style=\"width: 96%; height: 120px; text-align: left; margin-top: 10px; margin-right: 2%; margin-left: 2%; border-bottom-color: rgb(141, 105, 13); border-bottom-width: 3px; border-bottom-style: double; background-color: white;\">";
            html += "<tbody>";
            html += "<tr>";
            html += "<td>";
            html += " <img alt=\"Logo Rosario Finanzas\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/RosarioNews.jpg\" style=\"width: 174px; height: 44px;\" /></td>";
            html += "<td style=\"text-align: right;\">";
            html += "<span style=\"font-family: arial,helvetica,sans-serif; font-size: 24px;\"><strong>FinanciaPyme 2015</strong></span><br />";
            html += "<br />";
            html += "<span style=\"color: black; font-family: arial,helvetica,sans-serif; font-size: 14px;\"><a href=\"http://rosariofinanzas.com.ar/FinanciaPyme/FinanciaPyme2015.html\" style=\"text-decoration: none;\">FinanciaPyme 2015</a></span></td>";
            html += "</tr>";
            html += "</tbody>";
            html += "</table>";

            // CUERPO
            html += "<p>";
            html += "Muchas gracias " + nombre + " por inscribirte a <strong>FinanciaPyme 2015</strong>. Ya estas habilitado/a para asistir al evento.";
            html += "</p>";


            html += "<p>";
            html += "Te esperamos el jueves 26 de noviembre de 17:30 hs a 21:30 hs en San Martín 75 Planta Alta, Venado Tuerto";
            html += "</p>";


            html += "<div style=\"padding: 10px; height: 25px; text-align: right; background-color: rgb(141, 105, 13);\">";
            html += "<a href=\"https://www.facebook.com/rosario.finanzas\" target=\"_blank\"><img alt=\"Facebook\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/face.png\" style=\"border-width: 0px; border-style: solid; width: 23px; height: 22px;\" /></a> <a href=\"https://twitter.com/rosariofinanzas\" target=\"_blank\"> <img alt=\"Twitter\" border=\"0\" src=\"http://rosariofinanzas.com.ar/ckfinder/images/twit.png\" style=\"border-width: 0px; border-style: solid; width: 23px; height: 22px;\" /></a></div>";
            html += "</div>";
            html += "</div>";
            html += "<p>";
            html += "</p>";

            return html;
        }


        private string LinkExtension(string Link)
        {
            return Link.Substring(Link.LastIndexOf(".") + 1);
        }


        private Stream Link(string link)
        {

            //This controls how many bytes to read at a time and send to the client
            int bytesToRead = 10000;

            // Buffer to read bytes in chunk size specified above
            byte[] buffer = new Byte[bytesToRead];

            WebClient wc = new WebClient();
            byte[] b = wc.DownloadData(link);

            Stream ms = new MemoryStream(b);

            return ms;
        }

        [WebMethod]
        public string InscripcionPersonaCurso(string nombre, string apellido, string email, string telefono, string dni, string curso, string urlReferencia)
        {
            try
            {
                               
                string _rta = "0";

                // Inserta el registro de la Inscripcion
                var cn = new ClsConeccion();
                cn.Abrir();

                cn.AgregarParametros("Nombre", nombre);
                cn.AgregarParametros("Apellido", apellido);
                cn.AgregarParametros("DNI", dni.Trim());
                cn.AgregarParametros("Email", email.Trim());
                cn.AgregarParametros("Telefono", telefono);
                cn.AgregarParametros("Profesion", "");

                cn.AgregarParametros("ProfesionDescripcion", "");
                cn.AgregarParametros("Extra", "");
                cn.AgregarParametros("Invitado", "");
                cn.AgregarParametros("Publicidad", "");
                cn.AgregarParametros("Comentarios", "");
                cn.AgregarParametros("Evento", curso);
                cn.AgregarParametros("MedioPago", "Deposito");
                cn.AgregarParametros("EstadoPago", "Pendiente");
                cn.AgregarParametros("UrlDeReferencia", urlReferencia);

                var rd = cn.EjecutarSpDataReader("sp_ADM_NewPersonaCurso");

                while (rd.Read())
                {
                    _rta = Convert.ToString(rd[0]);
                }
                cn.Cerrar();

                cn.Abrir();

                var rd2 = cn.EjecutarSqlDataReader("Select Descripcion, FechaHoraParaEmail, Lugar, Importe, CantJornadas, EsPago from Cursos where alias = '" + curso + "'");


                var dt = new DataTable();
                dt.Load(rd2);                

                // Envía email de confirmacion al inscripto
                var correo = new System.Net.Mail.MailMessage
                {
                    From = new System.Net.Mail.MailAddress("info@rosariofinanzas.com.ar")
                };

                correo.To.Add(email.Trim());
                correo.Subject = "Curso '" + dt.Rows[0][0].ToString() + "': Inscripción satisfactoria";

                string Body = "";

                if(Convert.ToBoolean(dt.Rows[0][5]) == true)
                    Body = GetHtmlEmailInscripcionCursoPago(dt.Rows[0][0].ToString(), dt.Rows[0][1].ToString(), dt.Rows[0][2].ToString(), dt.Rows[0][3].ToString(), dt.Rows[0][4].ToString());
                else
                    Body = GetHtmlEmailInscripcionCursoGratuito(dt.Rows[0][0].ToString(), dt.Rows[0][1].ToString(), dt.Rows[0][2].ToString(), dt.Rows[0][3].ToString(), dt.Rows[0][4].ToString());


                cn.Cerrar();

                AlternateView htmlView =
                AlternateView.CreateAlternateViewFromString(Body,
                                        Encoding.UTF8,
                                        MediaTypeNames.Text.Html);

                correo.AlternateViews.Add(htmlView);

                correo.Priority = System.Net.Mail.MailPriority.Normal;

                //var smtp = new System.Net.Mail.SmtpClient
                //{
                //    EnableSsl = true,
                //    Host = "smtp.gmail.com",
                //    Port = 587,
                //    Credentials = new System.Net.NetworkCredential("rf2.rosariofinanzas@gmail.com", "rosfin2011")
                //};

                var smtp = new System.Net.Mail.SmtpClient
                {
                    Host = "localhost"
                };

                smtp.Send(correo);                              

                return _rta;
            }
            catch (Exception ex)
            {

                throw ex;

            }
        }


        [WebMethod]
        public string GetItemsTablaPrincipal()
        {
            // Acciones e indices a obtener
            string quotes = "ALUA.BA,ERAR.BA,APBR.BA,PBR,TS.BA,TS,YPFD.BA,YPF,PESA.BA,COME.BA,GGAL.BA,GGAL,BMA.BA,BMA,FRAN.BA,BFR,PAMP.BA,PAM,EDN.BA,EDN,TRAN.BA,MOLI.BA,MIRG.BA,TECO2.BA,TEO";


            VerificarCotizacionesDeReferencia(quotes, "Accion");


            string result = GetQuote(quotes, "Accion", "");

            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(result);
            
            foreach (XmlElement stock in xmlDoc.SelectNodes("//Stock"))
            {
                foreach (XmlElement currentItem in stock)
                {
                    switch (currentItem.InnerText)
                    {
                        case "^MERV":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "MERVAL";
                                stock.ReplaceChild(newvalue, currentItem); 
                            }
                            continue;

                        case "PBR":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "PBR";
                                stock.ReplaceChild(newvalue, currentItem);
                            }
                            continue;

                            case "TS":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "TS (ADR)";
                                stock.ReplaceChild(newvalue, currentItem); 
                            }
                            continue;

                            case "YPF":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "YPF (ADR)";
                                stock.ReplaceChild(newvalue, currentItem);
                            }
                            continue;

                            case "EDN":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "EDN (ADR)";
                                stock.ReplaceChild(newvalue, currentItem);
                            }
                            continue;

                            case "PAM":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "PAMP (ADR)";
                                stock.ReplaceChild(newvalue, currentItem);
                            }
                            continue;

                            case "GGAL":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "GGAL (ADR)";
                                stock.ReplaceChild(newvalue, currentItem);
                            }
                            continue;

                            case "BMA":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "BMA (ADR)";
                                stock.ReplaceChild(newvalue, currentItem);
                            }
                            continue;

                            case "BFR":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "FRAN (ADR)";
                                stock.ReplaceChild(newvalue, currentItem);
                            }
                            continue;

                            case "TEO":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "TECO2 (ADR)";
                                stock.ReplaceChild(newvalue, currentItem);
                            }
                            continue;

                        default:
                            continue;
                    }

                }
            }
            return Newtonsoft.Json.JsonConvert.SerializeXmlNode(xmlDoc);
        }

        [WebMethod]
        public string GetItemsTablaIndices()
        {
            // Acciones e indices a obtener
            string quotes = "^IXIC,^GSPC,^MERV,^BVSP,^FTSE,^GDAXI,^N225";

            VerificarCotizacionesDeReferencia("^GDAXI,^IXIC,^GSPC,^MERV,^BVSP,^DJI,^SSEC,^IBEX,^FTSE,^N225", "Indice");

            var quotesFromGoogleFinance = GetQuoteFromGoogleFinance("^DJI,^SSEC,^IBEX", "INDEXDJX:.DJI,SHA:000001,INDEXBME:IB", "Indice");
            
            string result = GetQuote(quotes, "Indice", quotesFromGoogleFinance);




            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(result);

            foreach (XmlElement stock in xmlDoc.SelectNodes("//Stock"))
            {
                foreach (XmlElement currentItem in stock)
                {
                    switch (currentItem.InnerText)
                    {
                        case "^MERV":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "MERVAL";
                                stock.ReplaceChild(newvalue, currentItem);
                            }
                            continue;

                        case "^GSPC":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "S&P500";
                                stock.ReplaceChild(newvalue, currentItem);
                            }
                            continue;

                        case "^BVSP":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "Bovespa";
                                stock.ReplaceChild(newvalue, currentItem);
                            }
                            continue;

                        case "^IXIC":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "Nasdaq";
                                stock.ReplaceChild(newvalue, currentItem);
                            }
                            continue;

                        case "^DJI":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "Dow Jones";
                                stock.ReplaceChild(newvalue, currentItem);
                            }
                            continue;

                        case "^SSEC":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "Shanghai SE Index";
                                stock.ReplaceChild(newvalue, currentItem);
                            }
                            continue;

                        case "^IBEX":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "Ibex 35";
                                stock.ReplaceChild(newvalue, currentItem);
                            }
                            continue;

                        case "^FTSE":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "FTSE 100";
                                stock.ReplaceChild(newvalue, currentItem);
                            }
                            continue;

                        case "^GDAXI":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "DAX";
                                stock.ReplaceChild(newvalue, currentItem);
                            }
                            continue;

                        case "^N225":
                            {
                                XmlNode newvalue = xmlDoc.CreateElement("Symbol");
                                newvalue.InnerText = "Nikkei 225";
                                stock.ReplaceChild(newvalue, currentItem);
                            }
                            continue;

                        default:
                            continue;
                    }

                }
            }
            return Newtonsoft.Json.JsonConvert.SerializeXmlNode(xmlDoc);
        }




        [WebMethod]   
        public string GetItemsTablaBonosUSD(string dolarMEP)
        {
            // Acciones e indices a obtener
            string quotes = "";
            


            
                if(DateTime.Now < new DateTime(2017, 04, 17))
                    quotes = "AA17.BA,AA17D.BA,GJ17.BA,GJ17D.BA,AN18.BA,AN18D.BA,AO20.BA,AO20D.BA,AY24.BA,AY24D.BA,DICA.BA,DICY.BA,PARA.BA";
                else
                {                
                    if(DateTime.Now < new DateTime(2017, 06, 02))
                        quotes = "GJ17.BA,GJ17D.BA,AN18.BA,AN18D.BA,AO20.BA,AO20D.BA,AY24.BA,AY24D.BA,DICA.BA,DICY.BA,PARA.BA";
                
                    else
                    {
                        if(DateTime.Now < new DateTime(2018, 11, 29))
                            quotes = "AN18.BA,AN18D.BA,AO20.BA,AO20D.BA,AY24.BA,AY24D.BA,DICA.BA,DICY.BA,PARA.BA";

                        else
                        {

                            if (DateTime.Now < new DateTime(2020, 10, 04))
                                quotes = "AO20.BA,AO20D.BA,AY24.BA,AY24D.BA,DICA.BA,DICY.BA,PARA.BA";

                            else
                            {

                                if (DateTime.Now < new DateTime(2024, 05, 07))
                                    quotes = "AY24.BA,AY24D.BA,DICA.BA,DICY.BA,PARA.BA";
                                else
                                {
                                    if (DateTime.Now < new DateTime(2033, 12, 31))
                                        quotes = "DIA0.BA,DIA0D.BA,DICA.BA,DICAD.BA,DICY.BA,DICYD.BA,PARA.BA,PARAD.BA";
                                    else
                                    {
                                        if (DateTime.Now < new DateTime(2038, 12, 31))
                                            quotes = "PARA.BA,PARAD.BA";
                                    }
                                }
                            }
                        }
                    }
                }            
                 


            List<CashFlow> CuponesDiscount = new List<CashFlow>();
            List<CashFlow> CuponesPar = new List<CashFlow>();
            // se obtienen los CashFlows de los Bonos Discount y Par
            var cn = new ClsConeccion();
            
            cn.Abrir();
            cn.AgregarParametros("codigoBono", "DIA0");
            var sqldrCuponesDIA0 = cn.EjecutarSpDataReader("sp_ADM_GetCuponesBono");
            var dtCuponesDIA0 = new DataTable();
            dtCuponesDIA0.Load(sqldrCuponesDIA0);
            cn.Cerrar();

            for (int i = 0; i < dtCuponesDIA0.Rows.Count; i++)
			{
                CuponesDiscount.Add(new CashFlow(Convert.ToDouble(dtCuponesDIA0.Rows[i]["MontoPago"].ToString().Replace(".", ",")), Convert.ToDateTime(dtCuponesDIA0.Rows[i]["FechaPago"])));
			}


            cn.Abrir();
            cn.AgregarParametros("codigoBono", "PAA0");
            var sqldrCuponesPAA0 = cn.EjecutarSpDataReader("sp_ADM_GetCuponesBono");
            var dtCuponesPAA0 = new DataTable();
            dtCuponesPAA0.Load(sqldrCuponesPAA0);
            cn.Cerrar();

            for (int i = 0; i < dtCuponesPAA0.Rows.Count; i++)
            {
                CuponesPar.Add(new CashFlow(Convert.ToDouble(dtCuponesPAA0.Rows[i]["MontoPago"].ToString().Replace(".", ",")), Convert.ToDateTime(dtCuponesPAA0.Rows[i]["FechaPago"])));
            }

            
            string result = GetQuoteBonds(quotes, CuponesDiscount, CuponesPar, Convert.ToDouble(dolarMEP));

            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(result);

            return Newtonsoft.Json.JsonConvert.SerializeXmlNode(xmlDoc);
        }



        
        /// <summary>
        /// This function handles and parses multiple stock symbols as input parameters 
        /// and builds a valid XML return document.
        /// </summary>
        /// <param name="symbol">A bunch of stock symbols seperated by space or comma</param>
        /// <returns>Return stock quote data in XML format</returns>
        /// 
        /*

     Códigos para obtener información desde Yahoo!
"a":   "Ask"
"a2":   "Average Daily Volume"
"a5":   "Ask Size"
"b":   "Bid"
"b2":   "Ask (Real-time)"
"b3":   "Bid (Real-time)"
"b4":   "Book Value"
"b6":   "Bid Size"
"c":   "Change & Percent Change"
"c1":   "Change"
"c3":   "Commission"
"c6":   "Change (Real-time)"
"c8":   "After Hours Change (Real-time)"
"d":   "Dividend/Share"
"d1":   "Last Trade Date"
"d2":   "Trade Date"
"e":   "Earnings/Share"
"e1":   "Error Indication (returned for symbol changed / invalid)"
"e7":   "EPS Estimate Current Year"
"e8":   "EPS Estimate Next Year"
"e9":   "EPS Estimate Next Quarter"
"f6":   "Float Shares"
"g":   "Day's Low"
"h":   "Day's High"
"j":   "52-week Low"
"k":   "52-week High"
"g1":   "Holdings Gain Percent"
"g3":   "Annualized Gain"
"g4":   "Holdings Gain"
"g5":   "Holdings Gain Percent (Real-time)"
"g6":   "Holdings Gain (Real-time)"
"i":   "More Info"
"i5":   "Order Book (Real-time)"
"j1":   "Market Capitalization"
"j3":   "Market Cap (Real-time)"
"j4":   "EBITDA"
"j5":   "Change From 52-week Low"
"j6":   "Percent Change From 52-week Low"
"k1":   "Last Trade (Real-time With Time"
"k2":   "Change Percent (Real-time)"
"k3":   "Last Trade Size"
"k4":   "Change From 52-week High"
"k5":   "Percent Change From 52-week High"
"l":   "Last Trade (With Time)"
"l1":   "Last Trade (Price Only)"
"l2":   "High Limit"
"l3":   "Low Limit"
"m":   "Day's Range"
"m2":   "Day's Range (Real-time)"
"m3":   "50-day Moving Average"
"m4":   "200-day Moving Average"
"m5":   "Change From 200-day Moving Average"
"m6":  "Percent Change From 200-day Moving Average"
"m7":   "Change From 50-day Moving Average"
"m8":   "Percent Change From -day Moving Average"
"n":   "Name"
"n4":   "Notes"
"o":   "Open"
"p":   "Previous Close"
"p1":   "Price Paid"
"p2":   "Change in Percent"
"p5":   "Price/Sales"
"p6":   "Price/Book"
"q":   "Ex-Dividend Date"
"r":   "P/E Ratio"
"r1":   "Dividend Pay Date"
"r2":   "P/E Ratio (Real-time)"
"r5":   "PEG Ratio"
"r6":   "Price/EPS Estimate Current Year"
"r7":   "Price/EPS Estimate Next Year"
"s":   "Symbol"
"s1":   "Shares Owned"
"s7":   "Short Ratio"
"t1":   "Last Trade Time"
"t6":   "Trade Links"
"t7":   "Ticker Trend"
"t8":   " yr Target Price"
"v":   "Volume"
"v1":   "Holdings Value"
"v7":   "Holdings Value (Real-time)"
"w":   "52-week Range"
"w1":   "Day's Value Change"
"w4":   "Day's Value Change (Real-time)"
"x":   "Stock Exchange"
"y":   "Dividend Yield"
        */

        public static string GetQuote(string symbol, string tipoInstrumento, string indicesGoogleFinance)
        {
            // Set the return string to null.
            string result = null;
            try
            {

                // Obtener último día hábil del año anterior
                var lastMarketDayOfLastYear = new DateTime(DateTime.Now.Year - 1, 12, 31);
                
                if (new DateTime(DateTime.Now.Year, 1, 2).DayOfWeek == DayOfWeek.Saturday)
                {
                    lastMarketDayOfLastYear = lastMarketDayOfLastYear.AddDays(-1);

                }
                else
                {
                    if (new DateTime(DateTime.Now.Year, 1, 2).DayOfWeek == DayOfWeek.Sunday)
                    {
                        lastMarketDayOfLastYear = lastMarketDayOfLastYear.AddDays(-2);
                    }

                }
                
                string[] symbols = symbol.Replace(",", " ").Split(' ');
                List<Decimal> valueFirstMarketDayOfYear = new List<decimal>();
                List<Decimal> valueFirstMarketDayOfMonth = new List<decimal>();


                if (DateTime.Now.Date > lastMarketDayOfLastYear.Date.AddDays(1))
                {

                    // Obtener las cotizaciones del primer dia del año. Si no están guardadas, hay que buscarlas en Yahoo!
                    // y grabarlas

                    var cn = new ClsConeccion();


                    cn.Abrir();
                    cn.AgregarParametros("fecha", lastMarketDayOfLastYear.Date.ToShortDateString());
                    cn.AgregarParametros("tipo", tipoInstrumento);
                    var sqldrCotizacionesfirstMarketDayOfYear = cn.EjecutarSpDataReader("sp_ADM_GetCotizacionByFechaAndTipo");
                    var dtCotizacionesfirstMarketDayOfYear = new DataTable();
                    dtCotizacionesfirstMarketDayOfYear.Load(sqldrCotizacionesfirstMarketDayOfYear);
                    cn.Cerrar();

                  
                    for (int i = 0; i < symbols.Length; i++)
                    {

                        foreach (DataRow item in dtCotizacionesfirstMarketDayOfYear.Rows)
                        {

                            if (symbols[i].Trim() == item["Codigo"].ToString())
                            {
                                valueFirstMarketDayOfYear.Add(Convert.ToDecimal(item["Cotizacion"]));
                                break;
                            }
                        }
                    }

                }


                // Obtener primer día hábil del mes
                var firstMarketDayOfMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
                var lastMarketDayOfLastMonth = firstMarketDayOfMonth.AddDays(-1);


                if (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).DayOfWeek == DayOfWeek.Monday)
                {
                    lastMarketDayOfLastMonth = firstMarketDayOfMonth.AddDays(-3);
                }
                else
                {
                    if (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).DayOfWeek == DayOfWeek.Sunday)
                    {
                        lastMarketDayOfLastMonth = firstMarketDayOfMonth.AddDays(-2);
                    }

                }

                if (DateTime.Now.Date > lastMarketDayOfLastMonth.Date)
                {
                    
                    // Obtener las cotizaciones del primer dia del Mes. Si no están guardadas, hay que buscarlas en Yahoo!
                    // y grabarlas

                    var cn = new ClsConeccion();

                    cn.Abrir();
                    cn.AgregarParametros("fecha", lastMarketDayOfLastMonth.Date.ToShortDateString());
                    cn.AgregarParametros("tipo", tipoInstrumento);
                    var sqldrCotizacionesfirstMarketDayOfMonth = cn.EjecutarSpDataReader("sp_ADM_GetCotizacionByFechaAndTipo");
                    var dtCotizacionesfirstMarketDayOfMonth = new DataTable();
                    dtCotizacionesfirstMarketDayOfMonth.Load(sqldrCotizacionesfirstMarketDayOfMonth);
                    cn.Cerrar();
                    

                    for (int i = 0; i < symbols.Length; i++)
                    {

                        foreach (DataRow item in dtCotizacionesfirstMarketDayOfMonth.Rows)
                        {

                            if (symbols[i].Trim() == item["Codigo"].ToString())
                            {
                                valueFirstMarketDayOfMonth.Add(Convert.ToDecimal(item["Cotizacion"]));
                                break;
                            }
                        }
                    }

                }

                // Use Yahoo finance service to download stock data from Yahoo
                string yahooURL = @"http://download.finance.yahoo.com/d/quotes.csv?s=" + symbol + "&f=sl1c1p2n"; // Obtiene el símbolo, último precio, cambio de precio y cambio en %
                               

                // Initialize a new WebRequest.
                HttpWebRequest webreq = (HttpWebRequest)WebRequest.Create(yahooURL);
                // Get the response from the Internet resource.
                HttpWebResponse webresp = (HttpWebResponse)webreq.GetResponse();
                // Read the body of the response from the server.
                StreamReader strm = new StreamReader(webresp.GetResponseStream(), Encoding.ASCII);

                // Construct a XML in string format.
                string tmp = "<Quotes>";
                
                // Si estamos cargando índices debemos cargar primero el DowJones
                if (indicesGoogleFinance != "")
                    tmp += indicesGoogleFinance;
                             

                string content = "";
                for (int i = 0; i < symbols.Length; i++)
                {
                    // Loop through each line from the stream, building the return XML Document string
                    if (symbols[i].Trim() == "")
                        continue;

                    content = strm.ReadLine().Replace("\"", "");
                    string[] contents = content.ToString().Split(',');
                    // If contents[2] = "N/A". the stock symbol is invalid.
                    if (contents[0] == "N/A")
                    {
                        // Construct XML via strings.
                        tmp += "<Stock>";
                        // "<" and ">" are illegal in XML elements. Replace the characters "<" and ">" to "&gt;" and "&lt;".
                        tmp += "<Code></Code>";
                        tmp += "<Symbol></Symbol>";
                        tmp += "<Price></Price>";
                        tmp += "<Currency></Currency>";
                        tmp += "<ChangeDay></ChangeDay>";
                        tmp += "<ChangeMonth></ChangeMonth>";
                        tmp += "<ChangeYear></ChangeYear>";
                        tmp += "</Stock>";
                    }
                    else
                    {
                        //construct XML via strings.
                        tmp += "<Stock>";
                        tmp += "<Code>." + contents[0].Replace(".BA", "").Replace("^", "") + "</Code>";
                        tmp += "<Symbol>" + contents[0] + "</Symbol>";

                        if (contents[1] != "N/A")
                        {
                            try
                            {
                                tmp += "<Price>" + Math.Round(Convert.ToDecimal(contents[1].Replace(".", ",").Replace("$ ", "")), 2) + "</Price>";
                            }
                            catch
                            {
                                tmp += "<Price>" + contents[1].Replace(".", ",") + "</Price>";
                            }
                        }
                        else 
                        {
                            tmp += "<Price> - </Price>";
                        
                        }

                        tmp += "<Currency>AR$</Currency>";

                        //if (contents[2] != "N/A")
                        //    tmp += "<PriceVariation>" + Math.Round(Convert.ToDecimal(contents[2].Replace(".", ",").Replace("%", "")), 2) + "</PriceVariation>";
                        //else
                        //    tmp += "<PriceVariation> - </PriceVariation>";
                        
                        if (contents[3] != "N/A")
                            tmp += "<ChangeDay>" + Math.Round(Convert.ToDecimal(contents[3].Replace(".", ",").Replace("%", "")), 2) + "</ChangeDay>";                       
                        else
                            tmp += "<ChangeDay> - </ChangeDay>";


                        if (DateTime.Now.Date > lastMarketDayOfLastMonth.Date)
                            tmp += "<ChangeMonth>" + Math.Round(((Convert.ToDecimal(contents[1].Replace(".", ",").Replace("$ ", "")) - valueFirstMarketDayOfMonth[i]) / valueFirstMarketDayOfMonth[i]) * 100, 2) + "</ChangeMonth>";
                        else
                            tmp += "<ChangeMonth>" + Math.Round(Convert.ToDecimal(contents[3].Replace(".", ",").Replace("%", "")), 2) + "</ChangeMonth>";


                        if (DateTime.Now.Date > lastMarketDayOfLastYear.Date.AddDays(1))
                            tmp += "<ChangeYear>" + Math.Round(((Convert.ToDecimal(contents[1].Replace(".", ",").Replace("$ ", "")) - valueFirstMarketDayOfYear[i]) / valueFirstMarketDayOfYear[i]) * 100, 2) + "</ChangeYear>";
                        else
                            tmp += "<ChangeYear>" + Math.Round(Convert.ToDecimal(contents[3].Replace(".", ",").Replace("%", "")), 2) + "</ChangeYear>";


                        tmp += "<Name>-</Name>";
                        // tmp += "<Name>" + contents[4] + "</Name>";     

                        tmp += "</Stock>";
                    }
                    // Set the return string
                    result += tmp;
                    tmp = "";
                }
                // Set the return string
                result += "</Quotes>";
                // Close the StreamReader object.
                strm.Close();
            }
            catch(Exception ex)
            {
                var temp = ex.Message;
            }
            // Return the stock quote data in XML format.

            return result;
        }

        // Yahoo Finance no permite obtener la información de algunos índices a traves de su API.
        // Por lo tanto debemos obtenerla desde google finance
        public static string GetQuoteFromGoogleFinance(string symbolYahoo, string symbolGoogle, string tipoInstrumento)
        {
            string tmp = "";
            try
            {
                // Obtener último día hábil del año anterior
                var lastMarketDayOfLastYear = new DateTime(DateTime.Now.Year - 1, 12, 31);

                if (new DateTime(DateTime.Now.Year, 1, 2).DayOfWeek == DayOfWeek.Saturday)
                {
                    lastMarketDayOfLastYear = lastMarketDayOfLastYear.AddDays(-1);
                }
                else
                {
                    if (new DateTime(DateTime.Now.Year, 1, 2).DayOfWeek == DayOfWeek.Sunday)
                    {
                        lastMarketDayOfLastYear = lastMarketDayOfLastYear.AddDays(-2);
                    }
                }

                string[] YahooSymbolsSplit = symbolYahoo.Replace(",", " ").Split(' ');
                List<Decimal> valueFirstMarketDayOfYear = new List<decimal>();
                List<Decimal> valueFirstMarketDayOfMonth = new List<decimal>();

                if (DateTime.Now.Date > lastMarketDayOfLastYear.Date.AddDays(1))
                {

                    // Obtener las cotizaciones del primer dia del año. Si no están guardadas, hay que buscarlas en Yahoo!
                    // y grabarlas

                    var cn = new ClsConeccion();

                    cn.Abrir();
                    cn.AgregarParametros("fecha", lastMarketDayOfLastYear.Date.ToShortDateString());
                    cn.AgregarParametros("tipo", tipoInstrumento);
                    var sqldrCotizacionesfirstMarketDayOfYear = cn.EjecutarSpDataReader("sp_ADM_GetCotizacionByFechaAndTipo");
                    var dtCotizacionesfirstMarketDayOfYear = new DataTable();
                    dtCotizacionesfirstMarketDayOfYear.Load(sqldrCotizacionesfirstMarketDayOfYear);
                    cn.Cerrar();

                    for (int i = 0; i < YahooSymbolsSplit.Length; i++)
                    {

                        foreach (DataRow item in dtCotizacionesfirstMarketDayOfYear.Rows)
                        {

                            if (YahooSymbolsSplit[i].Trim() == item["Codigo"].ToString())
                            {
                                valueFirstMarketDayOfYear.Add(Convert.ToDecimal(item["Cotizacion"]));
                                break;
                            }
                        }
                    }
                }

                // Obtener primer día hábil del mes
                var firstMarketDayOfMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
                var lastMarketDayOfLastMonth = firstMarketDayOfMonth.AddDays(-1);


                if (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).DayOfWeek == DayOfWeek.Monday)
                {
                    lastMarketDayOfLastMonth = firstMarketDayOfMonth.AddDays(-3);
                }
                else
                {
                    if (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).DayOfWeek == DayOfWeek.Sunday)
                    {
                        lastMarketDayOfLastMonth = firstMarketDayOfMonth.AddDays(-2);
                    }

                }

                if (DateTime.Now.Date > lastMarketDayOfLastMonth.Date)
                {

                    // Obtener las cotizaciones del primer dia del Mes. Si no están guardadas, hay que buscarlas en Yahoo!
                    // y grabarlas

                    var cn = new ClsConeccion();

                    cn.Abrir();
                    cn.AgregarParametros("fecha", lastMarketDayOfLastMonth.Date.ToShortDateString());
                    cn.AgregarParametros("tipo", "Indice");
                    var sqldrCotizacionesfirstMarketDayOfMonth = cn.EjecutarSpDataReader("sp_ADM_GetCotizacionByFechaAndTipo");
                    var dtCotizacionesfirstMarketDayOfMonth = new DataTable();
                    dtCotizacionesfirstMarketDayOfMonth.Load(sqldrCotizacionesfirstMarketDayOfMonth);
                    cn.Cerrar();


                    for (int i = 0; i < YahooSymbolsSplit.Length; i++)
                    {

                        foreach (DataRow item in dtCotizacionesfirstMarketDayOfMonth.Rows)
                        {

                            if (YahooSymbolsSplit[i].Trim() == item["Codigo"].ToString())
                            {
                                valueFirstMarketDayOfMonth.Add(Convert.ToDecimal(item["Cotizacion"]));
                                break;
                            }
                        }
                    }
                }


                // Use Yahoo finance service to download stock data from Yahoo
                string googleURL = @"http://www.google.com/finance/info?q=" + symbolGoogle; // Obtiene el símbolo, último precio, cambio de precio y cambio en %

                // Initialize a new WebRequest.
                HttpWebRequest webreq = (HttpWebRequest)WebRequest.Create(googleURL);
                // Get the response from the Internet resource.
                HttpWebResponse webresp = (HttpWebResponse)webreq.GetResponse();
                // Read the body of the response from the server.
                StreamReader strm = new StreamReader(webresp.GetResponseStream(), Encoding.ASCII);

                string content = strm.ReadToEnd();
                string content2 = content.Replace("\n", "").Replace("\"", "").Replace("// [{", "").Replace("{", "").Replace("}", "").Replace("[", "").Replace("]", "").Replace(",id", "").Replace(",l_fix", "");
                    
                string[] contentArray = content2.ToString().Split(':');

                string[] GoogleSymbolsSplit = symbolGoogle.Replace(",", " ").Split(' ');
                

                int j = 0;

                for (int i = 0; i < GoogleSymbolsSplit.Length; i++)
                {

                    j += 4;
                    Decimal valueIndex = Convert.ToDecimal(contentArray[j].Trim().Replace(",", "").Replace(".", ","));

                    j += 16;
                    Decimal valueLastDay = Convert.ToDecimal(contentArray[j].Trim().Replace(",", "").Replace(".", ","));


                    // Construct a XML in string format.
                    tmp += "<Stock>";
                    tmp += "<Code>." + YahooSymbolsSplit[i].Trim().Replace(".BA","").Replace("^","") + "</Code>";
                    tmp += "<Symbol>" + YahooSymbolsSplit[i].Trim() + "</Symbol>";


                    try
                    {
                        tmp += "<Price>" + Math.Round(valueIndex, 2) + "</Price>";
                    }
                    catch
                    {
                        tmp += "<Price>" + valueIndex + "</Price>";
                    }

                    tmp += "<Currency>U$S</Currency>";

                    tmp += "<ChangeDay>" + Math.Round(((valueIndex - valueLastDay) / valueLastDay) * 100, 2) + "</ChangeDay>";


                    if (DateTime.Now.Date > lastMarketDayOfLastMonth.Date)
                        tmp += "<ChangeMonth>" + Math.Round(((valueIndex - valueFirstMarketDayOfMonth[i]) / valueFirstMarketDayOfMonth[i]) * 100, 2) + "</ChangeMonth>";
                    else
                        tmp += "<ChangeMonth>" + Math.Round(((valueIndex - valueLastDay) / valueLastDay) * 100, 2) + "</ChangeMonth>";


                    if (DateTime.Now.Date > lastMarketDayOfLastYear.Date.AddDays(1))
                        tmp += "<ChangeYear>" + Math.Round(((valueIndex - valueFirstMarketDayOfYear[i]) / valueFirstMarketDayOfYear[i]) * 100, 2) + "</ChangeYear>";
                    else
                        tmp += "<ChangeYear>" + Math.Round(((valueIndex - valueLastDay) / valueLastDay) * 100, 2) + "</ChangeYear>";


                    tmp += "<Name></Name>";
                    
                    
                    tmp += "</Stock>";

                }
            }
            catch (Exception ex)
            {
                var temp = ex.Message;
            }


            return tmp;
        }


        public static string GetQuoteBonds(string symbol, List<CashFlow> CuponesDiscount, List<CashFlow> CuponesPar, double dolarMEP)
        {
            // Set the return string to null.
            string result = null;
            try
            {               

                string[] symbols = symbol.Replace(",", " ").Split(' ');

                // Use Yahoo finance service to download stock data from Yahoo
                string yahooURL = @"http://download.finance.yahoo.com/d/quotes.csv?s=" + symbol + "&f=sl1c1p2n"; // Obtiene el símbolo, último precio, cambio de precio y cambio en %


                // Initialize a new WebRequest.
                HttpWebRequest webreq = (HttpWebRequest)WebRequest.Create(yahooURL);
                // Get the response from the Internet resource.
                HttpWebResponse webresp = (HttpWebResponse)webreq.GetResponse();
                // Read the body of the response from the server.
                StreamReader strm = new StreamReader(webresp.GetResponseStream(), Encoding.ASCII);

                // Construct a XML in string format.
                string tmp = "<Quotes>";
                string content = "";
                for (int i = 0; i < symbols.Length; i++)
                {
                    // Loop through each line from the stream, building the return XML Document string
                    if (symbols[i].Trim() == "")
                        continue;

                    content = strm.ReadLine().Replace("\"", "");
                    string[] contents = content.ToString().Split(',');
                    // If contents[2] = "N/A". the stock symbol is invalid.
                    if (contents[0] == "N/A")
                    {
                        // Construct XML via strings.
                        tmp += "<Stock>";
                        // "<" and ">" are illegal in XML elements. Replace the characters "<" and ">" to "&gt;" and "&lt;".


                        tmp += "<Symbol></Symbol>";
                        tmp += "<Price></Price>";
                        tmp += "<Currency></Currency>";
                        tmp += "<ChangeDay></ChangeDay>";
                        tmp += "<ChangeMonth></ChangeMonth>";
                        tmp += "<ChangeYear></ChangeYear>";
                        tmp += "</Stock>";
                    }
                    else
                    {
                        //construct XML via strings.
                        tmp += "<Stock>";

                        tmp += "<Symbol>" + contents[0] + "</Symbol>";

                        if (contents[1] != "N/A")
                        {
                            try
                            {
                                tmp += "<Price>" + Math.Round(Convert.ToDecimal(contents[1].Replace(".", ",").Replace("$ ", "")), 2) + "</Price>";
                            }
                            catch
                            {
                                tmp += "<Price>" + contents[1].Replace(".", ",") + "</Price>";
                            }
                        }
                        else
                        {
                            tmp += "<Price> - </Price>";                        
                        }

                        tmp += "<Currency>AR$</Currency>";

                        //if (contents[2] != "N/A")
                        //    tmp += "<PriceVariation>" + Math.Round(Convert.ToDecimal(contents[2].Replace(".", ",").Replace("%", "")), 2) + "</PriceVariation>";
                        //else
                        //    tmp += "<PriceVariation> - </PriceVariation>";

                        if (contents[3] != "N/A")
                            tmp += "<ChangeDay>" + Math.Round(Convert.ToDecimal(contents[3].Replace(".", ",").Replace("%", "")), 2) + "</ChangeDay>";
                        else
                            tmp += "<ChangeDay> - </ChangeDay>";
                        
                        tmp += "<ChangeMonth> - </ChangeMonth>";
                        tmp += "<ChangeYear> - </ChangeYear>";
                        tmp += "<Name>" + contents[4] + "</Name>";
                                
                
                        // TIR
                        double tolerance = 0.0001;
                        int maxIters = 100;

                        switch (contents[0].ToString())
                        {

                            case "DIA0.BA":
                                {

                                    var cashFlows = new List<CashFlow>();

                                    cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                    cashFlows.AddRange(CuponesDiscount);

                                    var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                    tmp += "<IRR>" + Math.Round(irr.Value * 100, 2).ToString() + "</IRR>";

                                }
                                break;

                            case "DICA.BA":
                                {
                                    var cashFlows = new List<CashFlow>();

                                    cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now)); 
                                    cashFlows.AddRange(CuponesDiscount);

                                    var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                    tmp += "<IRR>" + Math.Round(irr.Value * 100, 2).ToString() + "</IRR>";

                                }
                                break;

                            case "DICY.BA":
                                {
                                    var cashFlows = new List<CashFlow>();

                                    cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                    cashFlows.AddRange(CuponesDiscount);

                                    var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                    tmp += "<IRR>" + Math.Round(irr.Value * 100, 2).ToString() + "</IRR>";

                                }
                                break;

                            case "DIY0.BA":
                                {
                                    var cashFlows = new List<CashFlow>();

                                    cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                    cashFlows.AddRange(CuponesDiscount);

                                    var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                    tmp += "<IRR>" + Math.Round(irr.Value * 100, 2).ToString() + "</IRR>";

                                }
                                break;

                            case "PAA0.BA":
                                {
                                    var cashFlows = new List<CashFlow>();

                                    cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                    cashFlows.AddRange(CuponesPar);

                                    var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                    tmp += "<IRR>" + Math.Round(irr.Value * 100, 2).ToString() + "</IRR>";

                                }
                                break;

                            case "PARA.BA":
                                {
                                    var cashFlows = new List<CashFlow>();

                                    cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                    cashFlows.AddRange(CuponesPar);

                                    var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                    tmp += "<IRR>" + Math.Round(irr.Value * 100, 2).ToString() + "</IRR>";

                                }
                                break;

                            case "PARY.BA":
                                {
                                    var cashFlows = new List<CashFlow>();

                                    cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                    cashFlows.AddRange(CuponesPar);

                                    var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                    tmp += "<IRR>" + Math.Round(irr.Value * 100, 2).ToString() + "</IRR>";

                                }
                                break;

                            case "PAY0.BA":
                                {
                                    var cashFlows = new List<CashFlow>();

                                    cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                    cashFlows.AddRange(CuponesPar);

                                    var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                    tmp += "<IRR>" + Math.Round(irr.Value * 100, 2).ToString() + "</IRR>";

                                }
                                break;

                            case "AO20.BA":
                                {
                                    List<CashFlow> Cupones = new List<CashFlow>();
                                    // se obtienen los CashFlows de los Bonos Discount y Par
                                    var cn = new ClsConeccion();

                                    cn.Abrir();
                                    cn.AgregarParametros("codigoBono", "AO20");
                                    var sqldrCupones = cn.EjecutarSpDataReader("sp_ADM_GetCuponesBono");
                                    var dtCupones = new DataTable();
                                    dtCupones.Load(sqldrCupones);
                                    cn.Cerrar();

                                    for (int j = 0; j < dtCupones.Rows.Count; j++)
                                    {
                                        Cupones.Add(new CashFlow(Convert.ToDouble(dtCupones.Rows[j]["MontoPago"].ToString().Replace(".", ",")), Convert.ToDateTime(dtCupones.Rows[j]["FechaPago"])));
                                    }

                                    var cashFlows = new List<CashFlow>();

                                    cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                    cashFlows.AddRange(Cupones);

                                    var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                    tmp += "<IRR>" + Math.Round(irr.Value * 100, 2).ToString() + "</IRR>";

                                }
                                break;

                            case "AA17.BA":
                                {

                                    List<CashFlow> Cupones = new List<CashFlow>();
                                    // se obtienen los CashFlows de los Bonos Discount y Par
                                    var cn = new ClsConeccion();

                                    cn.Abrir();
                                    cn.AgregarParametros("codigoBono", "AA17");
                                    var sqldrCupones = cn.EjecutarSpDataReader("sp_ADM_GetCuponesBono");
                                    var dtCupones = new DataTable();
                                    dtCupones.Load(sqldrCupones);
                                    cn.Cerrar();

                                    for (int j = 0; j < dtCupones.Rows.Count; j++)
                                    {
                                        Cupones.Add(new CashFlow(Convert.ToDouble(dtCupones.Rows[j]["MontoPago"].ToString().Replace(".", ",")), Convert.ToDateTime(dtCupones.Rows[j]["FechaPago"])));
                                    }

                                    var cashFlows = new List<CashFlow>();

                                    cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                    cashFlows.AddRange(Cupones);

                                    var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                    tmp += "<IRR>" + Math.Round(irr.Value * 100, 2).ToString() + "</IRR>";

                                }
                                break;

                            case "GJ17.BA":
                                {

                                    List<CashFlow> Cupones = new List<CashFlow>();
                                    // se obtienen los CashFlows de los Bonos Discount y Par
                                    var cn = new ClsConeccion();

                                    cn.Abrir();
                                    cn.AgregarParametros("codigoBono", "GJ17");
                                    var sqldrCupones = cn.EjecutarSpDataReader("sp_ADM_GetCuponesBono");
                                    var dtCupones = new DataTable();
                                    dtCupones.Load(sqldrCupones);
                                    cn.Cerrar();

                                    for (int j = 0; j < dtCupones.Rows.Count; j++)
                                    {
                                        Cupones.Add(new CashFlow(Convert.ToDouble(dtCupones.Rows[j]["MontoPago"].ToString().Replace(".", ",")), Convert.ToDateTime(dtCupones.Rows[j]["FechaPago"])));
                                    }

                                    var cashFlows = new List<CashFlow>();

                                    cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                    cashFlows.AddRange(Cupones);

                                    var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                    tmp += "<IRR>" + Math.Round(irr.Value * 100, 2).ToString() + "</IRR>";

                                }
                                break;

                            case "AN18.BA":
                                {
                                    List<CashFlow> Cupones = new List<CashFlow>();
                                    // se obtienen los CashFlows de los Bonos Discount y Par
                                    var cn = new ClsConeccion();

                                    cn.Abrir();
                                    cn.AgregarParametros("codigoBono", "AN18");
                                    var sqldrCupones = cn.EjecutarSpDataReader("sp_ADM_GetCuponesBono");
                                    var dtCupones = new DataTable();
                                    dtCupones.Load(sqldrCupones);
                                    cn.Cerrar();

                                    for (int j = 0; j < dtCupones.Rows.Count; j++)
                                    {
                                        Cupones.Add(new CashFlow(Convert.ToDouble(dtCupones.Rows[j]["MontoPago"].ToString().Replace(".", ",")), Convert.ToDateTime(dtCupones.Rows[j]["FechaPago"])));
                                    }

                                    var cashFlows = new List<CashFlow>();

                                    cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                    cashFlows.AddRange(Cupones);

                                    var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                    tmp += "<IRR>" + Math.Round(irr.Value * 100, 2).ToString() + "</IRR>";

                                }
                                break;

                            case "AY24.BA":
                                {
                                    List<CashFlow> Cupones = new List<CashFlow>();
                                    // se obtienen los CashFlows de los Bonos Discount y Par
                                    var cn = new ClsConeccion();

                                    cn.Abrir();
                                    cn.AgregarParametros("codigoBono", "AY24");
                                    var sqldrCupones = cn.EjecutarSpDataReader("sp_ADM_GetCuponesBono");
                                    var dtCupones = new DataTable();
                                    dtCupones.Load(sqldrCupones);
                                    cn.Cerrar();

                                    for (int j = 0; j < dtCupones.Rows.Count; j++)
                                    {
                                        Cupones.Add(new CashFlow(Convert.ToDouble(dtCupones.Rows[j]["MontoPago"].ToString().Replace(".", ",")), Convert.ToDateTime(dtCupones.Rows[j]["FechaPago"])));
                                    }

                                    var cashFlows = new List<CashFlow>();

                                    cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                    cashFlows.AddRange(Cupones);

                                    var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                    tmp += "<IRR>" + Math.Round(irr.Value * 100, 2).ToString() + "</IRR>";

                                }
                                break;

                            default:
                                tmp += "<IRR></IRR>";
                                break;
                        }

                        tmp += "</Stock>";
                    }
                    // Set the return string
                    result += tmp;
                    tmp = "";
                }
                // Set the return string
                result += "</Quotes>";
                // Close the StreamReader object.
                strm.Close();
            }
            catch (Exception ex)
            {
                var temp = ex.Message;
            }
            // Return the stock quote data in XML format.

            return result;
        }


         [WebMethod] 
        public decimal ObtenerCotizacionDolarA3500()
        {

            string quandlURL = @"https://www.quandl.com/api/v1/datasets/BCRA/REFER.csv";

            HttpWebRequest webreq = (HttpWebRequest)WebRequest.Create(quandlURL);
            // Get the response from the Internet resource.
            HttpWebResponse webresp = (HttpWebResponse)webreq.GetResponse();
            // Read the body of the response from the server.
            StreamReader strm = new StreamReader(webresp.GetResponseStream(), Encoding.ASCII);

            string content = strm.ReadToEnd().Replace("\n", ",");
            string[] contents = content.ToString().Split(',');

            return Math.Round(Convert.ToDecimal(contents[3].Replace(".",",")), 2); 
                         
         }





         [WebMethod]
         public string ObtenerCotizacionesPromedioRF()
         {

             var cn = new ClsConeccion();
             cn.Abrir();

             string sql = "Select varMoneda, Replace (Cast(Cast(PromedioRFCompra as Numeric(9,3)) as varchar),'.',',')  Compra, Replace (Cast(Cast(PromedioRFVenta as Numeric(9,3)) as varchar),'.',',')  Venta From Promedios P ";
             sql += "inner Join (Select Moneda, Max(Fecha) fec From Promedios ";
             sql += "group by Moneda) P1 on P1.Moneda = P.Moneda and P1.Fec = P.Fecha ";
             sql += "Inner Join Monedas M on P.Moneda = M.idMoneda order by P.Moneda ";

             var sqldr2 = cn.EjecutarSqlDataReader(sql);
             var dt = new DataTable();
             dt.Load(sqldr2);

             cn.Cerrar();

             string result = "<Monedas>";

             foreach (DataRow dr in dt.Rows)
             {

                 switch (dr["varMoneda"].ToString())
                 {
                     case "DÓLAR":
                         result += "<Moneda> <Nombre>DÓLAR</Nombre> <Cotizacion>" + Math.Round(Convert.ToDecimal(dr["Venta"]), 2).ToString() + "</Cotizacion> </Moneda> ";
                         break;

                     case "EURO":
                         result += "<Moneda> <Nombre>EURO</Nombre> <Cotizacion>" + Math.Round(Convert.ToDecimal(dr["Venta"]), 2).ToString() + "</Cotizacion> </Moneda> ";
                         break;

                     case "REAL":
                         result += "<Moneda> <Nombre>REAL</Nombre> <Cotizacion>" + Math.Round(Convert.ToDecimal(dr["Venta"]), 2).ToString() + "</Cotizacion> </Moneda> ";
                         break;

                     default:
                         break;
                 }
             }
             
             cn.Abrir();

             string sqlBlue = "SELECT * from CotizacionesActuales WHERE intOrden = 99999 AND intBanco = 29";
                         
             var sqldrBlue = cn.EjecutarSqlDataReader(sqlBlue);

             var dtBlue = new DataTable();

             dtBlue.Load(sqldrBlue);
             
             if (dtBlue.Rows.Count > 0)
             {
                 string temp = dtBlue.Rows[0]["monVenta"].ToString().Replace("$ ", "");
                 string temp2 = temp.Replace("$", "");

                 result += "<Moneda> <Nombre>BLUE</Nombre> <Cotizacion>" + Math.Round(Convert.ToDecimal(temp2), 2).ToString() + "</Cotizacion> </Moneda> ";
             }
             else
             {
                 result += "<Moneda> <Nombre>BLUE</Nombre> <Cotizacion> - </Cotizacion> </Moneda> ";             
             }

             result += "</Monedas>";

             cn.Cerrar();

             XmlDocument xmlDoc = new XmlDocument();
             xmlDoc.LoadXml(result);

             return Newtonsoft.Json.JsonConvert.SerializeXmlNode(xmlDoc);

         }

         public static void VerificarCotizacionesDeReferencia(string symbol, string tipoInstrumento)
         {

             // Obtener último día hábil del año anterior
             var lastMarketDayOfLastYear = new DateTime(DateTime.Now.Year - 1, 12, 31);

             if (new DateTime(DateTime.Now.Year, 1, 2).DayOfWeek == DayOfWeek.Saturday)
             {
                 lastMarketDayOfLastYear = lastMarketDayOfLastYear.AddDays(-1);

             }
             else
             {
                 if (new DateTime(DateTime.Now.Year, 1, 2).DayOfWeek == DayOfWeek.Sunday)
                 {
                     lastMarketDayOfLastYear = lastMarketDayOfLastYear.AddDays(-2);
                 }

             }

             string[] symbols = symbol.Replace(",", " ").Split(' ');
             List<Decimal> valueFirstMarketDayOfYear = new List<decimal>();
             List<Decimal> valueFirstMarketDayOfMonth = new List<decimal>();


             if (DateTime.Now.Date > lastMarketDayOfLastYear.Date.AddDays(1))
             {

                 // Obtener las cotizaciones del primer dia del año. Si no están guardadas, hay que buscarlas en Yahoo!
                 // y grabarlas

                 var cn = new ClsConeccion();


                 cn.Abrir();
                 cn.AgregarParametros("fecha", lastMarketDayOfLastYear.Date.ToShortDateString());
                 cn.AgregarParametros("tipo", tipoInstrumento);
                 var sqldrCotizacionesfirstMarketDayOfYear = cn.EjecutarSpDataReader("sp_ADM_GetCotizacionByFechaAndTipo");
                 var dtCotizacionesfirstMarketDayOfYear = new DataTable();
                 dtCotizacionesfirstMarketDayOfYear.Load(sqldrCotizacionesfirstMarketDayOfYear);
                 cn.Cerrar();

                 if (dtCotizacionesfirstMarketDayOfYear.Rows.Count == 0)
                 {

                     for (int i = 0; i < symbols.Length; i++)
                     {
                         // Loop through each line from the stream, building the return XML Document string
                         if (symbols[i].Trim() == "")
                             continue;

                        // Use Yahoo finance service to download stock data from Yahoo
                        string yahooURLFirstMarketDayOfYear = @"http://ichart.finance.yahoo.com/table.csv?s=" + symbols[i].Trim() + "&a=" + lastMarketDayOfLastYear.AddMonths(-1).Month.ToString() + "&b=" + lastMarketDayOfLastYear.Day.ToString() + "&c=" + lastMarketDayOfLastYear.Year.ToString() + "&d=" + lastMarketDayOfLastYear.AddMonths(-1).Month.ToString() + "&e=" + lastMarketDayOfLastYear.Day.ToString() + "&f=" + lastMarketDayOfLastYear.Year.ToString() + "&ignore=.csv";

                        
                         using (WebClient web = new WebClient())
                         {
                             string data = web.DownloadString(yahooURLFirstMarketDayOfYear);

                             data = data.Replace("\n", ",");

                             string[] rows = data.Split(',');

                             valueFirstMarketDayOfYear.Add(Convert.ToDecimal(rows[11].Replace(".", ",").Replace("$ ", "")));

                             cn.Abrir();
                             cn.AgregarParametros("codigo", symbols[i].Trim());
                             cn.AgregarParametros("fecha", lastMarketDayOfLastYear.Date.ToShortDateString());
                             cn.AgregarParametros("cotizacion", rows[11].Replace(".", ",").Replace("$ ", ""));
                             cn.AgregarParametros("tipo", tipoInstrumento);

                             var sqldrUpdateCotizacion = cn.EjecutarSpDataReader("sp_ADM_NewCotizacionReferencia");

                             cn.Cerrar();

                         }
                     }
                 }
             }


             // Obtener primer día hábil del mes
             var firstMarketDayOfMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
             var lastMarketDayOfLastMonth = firstMarketDayOfMonth.AddDays(-1);


             if (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).DayOfWeek == DayOfWeek.Monday)
             {
                 lastMarketDayOfLastMonth = firstMarketDayOfMonth.AddDays(-3);
             }
             else
             {
                 if (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).DayOfWeek == DayOfWeek.Sunday)
                 {
                     lastMarketDayOfLastMonth = firstMarketDayOfMonth.AddDays(-2);
                 }

             }

             if (DateTime.Now.Date > lastMarketDayOfLastMonth.Date)
             {

                 // Obtener las cotizaciones del primer dia del Mes. Si no están guardadas, hay que buscarlas en Yahoo!
                 // y grabarlas

                 var cn = new ClsConeccion();

                 cn.Abrir();
                 cn.AgregarParametros("fecha", lastMarketDayOfLastMonth.Date.ToShortDateString());
                 cn.AgregarParametros("tipo", tipoInstrumento);
                 var sqldrCotizacionesfirstMarketDayOfMonth = cn.EjecutarSpDataReader("sp_ADM_GetCotizacionByFechaAndTipo");
                 var dtCotizacionesfirstMarketDayOfMonth = new DataTable();
                 dtCotizacionesfirstMarketDayOfMonth.Load(sqldrCotizacionesfirstMarketDayOfMonth);
                 cn.Cerrar();

                 if (dtCotizacionesfirstMarketDayOfMonth.Rows.Count == 0)
                 {

                     for (int i = 0; i < symbols.Length; i++)
                     {
                         // Loop through each line from the stream, building the return XML Document string
                         if (symbols[i].Trim() == "")
                             continue;


                         // Use Yahoo finance service to download stock data from Yahoo
                         string yahooURLFirstMarketDayOfMonth = @"http://ichart.finance.yahoo.com/table.csv?s=" + symbols[i].Trim() + "&a=" + lastMarketDayOfLastMonth.AddMonths(-1).Month.ToString() + "&b=" + lastMarketDayOfLastMonth.Day.ToString() + "&c=" + lastMarketDayOfLastMonth.Year.ToString() + "&d=" + lastMarketDayOfLastMonth.AddMonths(-1).Month.ToString() + "&e=" + lastMarketDayOfLastMonth.Day.ToString() + "&f=" + lastMarketDayOfLastMonth.Year.ToString() + "&ignore=.csv";

                         using (WebClient web = new WebClient())
                         {
                             string data = web.DownloadString(yahooURLFirstMarketDayOfMonth);

                             data = data.Replace("\n", ",");

                             string[] rows = data.Split(',');

                             valueFirstMarketDayOfMonth.Add(Convert.ToDecimal(rows[11].Replace(".", ",").Replace("$ ", "")));


                             cn.Abrir();
                             cn.AgregarParametros("codigo", symbols[i].Trim());
                             cn.AgregarParametros("fecha", lastMarketDayOfLastMonth.Date.ToShortDateString());
                             cn.AgregarParametros("cotizacion", rows[11].Replace(".", ",").Replace("$ ", ""));
                             cn.AgregarParametros("tipo", tipoInstrumento);

                             var sqldrUpdateCotizacion = cn.EjecutarSpDataReader("sp_ADM_NewCotizacionReferencia");

                             cn.Cerrar();

                         }
                     }
                 }
             }
         }



        [WebMethod] 
        public double ObtenerCotizacionDolarMEP()
        {
            // Set the return string to null.
            double result = 0.00;
            try
            {
                // Use Yahoo finance service to download stock data from Yahoo
                string yahooURL = @"http://download.finance.yahoo.com/d/quotes.csv?s=RO15.BA,RO15D.BA,AA17.BA,AA17D.BA,AY24.BA,AY24D.BA&f=l1"; // Obtiene el símbolo, último precio, cambio de precio y cambio en %

                // Initialize a new WebRequest.
                HttpWebRequest webreq = (HttpWebRequest)WebRequest.Create(yahooURL);
                // Get the response from the Internet resource.
                HttpWebResponse webresp = (HttpWebResponse)webreq.GetResponse();
                // Read the body of the response from the server.
                StreamReader strm = new StreamReader(webresp.GetResponseStream(), Encoding.ASCII);

                string content = strm.ReadToEnd().Replace("\n", ",");
                string[] contents = content.ToString().Split(',');

                if (contents[0] != "N/A" && contents[1] != "N/A" && contents[2] != "N/A" && contents[3] != "N/A" && contents[4] != "N/A" && contents[5] != "N/A")
                {
                    result = (Convert.ToDouble(contents[0].Replace(".", ",")) / Convert.ToDouble(contents[1].Replace(".", ",")) + Convert.ToDouble(contents[2].Replace(".", ",")) / Convert.ToDouble(contents[3].Replace(".", ",")) + Convert.ToDouble(contents[4].Replace(".", ",")) / Convert.ToDouble(contents[5].Replace(".", ","))) / 3;
                }
                else
                {

                    // Use Yahoo finance service to download stock data from Yahoo
                    string yahooURL2 = @"http://download.finance.yahoo.com/d/quotes.csv?s=RO15.BA,RO15D.BA,AA17.BA,AA17D.BA,AY24.BA,AY24D.BA&f=p"; // Obtiene el símbolo, último precio, cambio de precio y cambio en %

                    // Initialize a new WebRequest.
                    HttpWebRequest webreq2 = (HttpWebRequest)WebRequest.Create(yahooURL2);
                    // Get the response from the Internet resource.
                    HttpWebResponse webresp2 = (HttpWebResponse)webreq2.GetResponse();
                    // Read the body of the response from the server.
                    StreamReader strm2 = new StreamReader(webresp2.GetResponseStream(), Encoding.ASCII);


                    string content2 = strm2.ReadToEnd().Replace("\n", ",");
                    string[] contents2 = content2.ToString().Split(',');

                    if (contents2[0] != "N/A" && contents2[1] != "N/A" && contents2[2] != "N/A" && contents2[3] != "N/A" && contents2[4] != "N/A" && contents2[5] != "N/A")
                    {
                        result = (Convert.ToDouble(contents2[0].Replace(".", ",")) / Convert.ToDouble(contents2[1].Replace(".", ",")) + Convert.ToDouble(contents2[2].Replace(".", ",")) / Convert.ToDouble(contents2[3].Replace(".", ",")) + Convert.ToDouble(contents2[4].Replace(".", ",")) / Convert.ToDouble(contents2[5].Replace(".", ","))) / 3;
                    }
                    else
                    {
                        result = 1;
                    }

                }
                
            }
            catch (Exception ex)
            { 
            
            
            }

            return  Math.Round(result, 2);

        }


        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string ObtenerInfoTooltip(string tipoInstrumento)
        {

            List<DataTooltip> datos = new List<DataTooltip>();


                var cnValNeg = new ClsConeccion();

                cnValNeg.Abrir();
                cnValNeg.AgregarParametros("tipo", tipoInstrumento);
                var sqldrValorNegociable = cnValNeg.EjecutarSpDataReader("sp_ADM_GetValoresVegociablesPorTipo");
                var dtValorNegociable = new DataTable();
                dtValorNegociable.Load(sqldrValorNegociable);

                cnValNeg.Cerrar();

                foreach (DataRow drValNeg in dtValorNegociable.Rows)      
                {

                    var item = new DataTooltip();

                    item.Codigo = drValNeg["Codigo"].ToString();
                    item.Nombre = drValNeg["Nombre"].ToString();
                    item.Descripcion = drValNeg["Descripcion"].ToString();
                    item.UrlImagen = drValNeg["UrlImagen"].ToString();
                    item.DireccionWeb = drValNeg["DireccionWeb"].ToString();

                    datos.Add(item);
                }

            return new JavaScriptSerializer().Serialize(datos); 

        }



        [WebMethod]
        public double ObtenerCotizacionCCL()
        {
            // Set the return string to null.
            double result = 0.00;
            try
            {
                string quotes = "";

                if (DateTime.Now < new DateTime(2015, 10, 03))
                    quotes = "RO15.BA,RO15C.BA";
                else
                {

                    if (DateTime.Now < new DateTime(2017, 04, 17))
                        quotes = "AA17.BA,AA17C.BA";
                    else
                    {

                        if (DateTime.Now < new DateTime(2024, 05, 07))
                            quotes = "AY24.BA,AY24C.BA";
                        else
                            return result;
                    }
                }

                // Use Yahoo finance service to download stock data from Yahoo
                string yahooURL = @"http://download.finance.yahoo.com/d/quotes.csv?s=" + quotes + "&f=l1"; // Obtiene el símbolo, último precio, cambio de precio y cambio en %

                // Initialize a new WebRequest.
                HttpWebRequest webreq = (HttpWebRequest)WebRequest.Create(yahooURL);
                // Get the response from the Internet resource.
                HttpWebResponse webresp = (HttpWebResponse)webreq.GetResponse();
                // Read the body of the response from the server.
                StreamReader strm = new StreamReader(webresp.GetResponseStream(), Encoding.ASCII);

                string content = strm.ReadToEnd().Replace("\n", ",");
                string[] contents = content.ToString().Split(',');

                if (contents[0] != "N/A" && contents[1] != "N/A" )
                {
                    result = Convert.ToDouble(contents[0].Replace(".", ",")) / Convert.ToDouble(contents[1].Replace(".", ","));
                }
                else
                {
                    // Use Yahoo finance service to download stock data from Yahoo
                    string yahooURL2 = @"http://download.finance.yahoo.com/d/quotes.csv?s=" + quotes + "&f=p"; // Obtiene el último precio

                    // Initialize a new WebRequest.
                    HttpWebRequest webreq2 = (HttpWebRequest)WebRequest.Create(yahooURL2);
                    // Get the response from the Internet resource.
                    HttpWebResponse webresp2 = (HttpWebResponse)webreq2.GetResponse();
                    // Read the body of the response from the server.
                    StreamReader strm2 = new StreamReader(webresp2.GetResponseStream(), Encoding.ASCII);


                    string content2 = strm2.ReadToEnd().Replace("\n", ",");
                    string[] contents2 = content2.ToString().Split(',');

                    if (contents2[0] != "N/A" && contents2[1] != "N/A")
                    {
                        result = Convert.ToDouble(contents2[0].Replace(".", ",")) / Convert.ToDouble(contents2[1].Replace(".", ","));
                    }
                    else
                    {
                        result = 1;
                    }

                }

            }
            catch (Exception ex)
            {


            }
            return Math.Round(result, 2);

        }

        [WebMethod]
        public string GetUltimaFecha()
        {
            string rta = "";

            var cn = new ClsConeccion();

            cn.Abrir();

            string sqlFecha = "SELECT MAX([Fecha]) as Fecha FROM HistoricoCotizaciones";

            var sqlDrFecha = cn.EjecutarSqlDataReader(sqlFecha);

            var dtFecha = new DataTable();

            dtFecha.Load(sqlDrFecha);

            if (dtFecha.Rows.Count > 0)
            {

                rta = Convert.ToDateTime(dtFecha.Rows[0]["Fecha"]).ToShortDateString();
            }

            cn.Cerrar();

            return rta;

        }


        [WebMethod]
        public string GetItemsHistoricos(string fecha, string tipo)
        {

            string result = "";
            XmlDocument xmlDoc = new XmlDocument();

            try
            {
                // Cuando la web se carga, si no tiene fecha enviada en la QUERY STRING hay que traer el último reporte guardado
                var cn = new ClsConeccion();

                cn.Abrir();

                var dt = new DataTable();

                if (fecha != string.Empty)
                {
                    cn.AgregarParametros("fecha", Convert.ToDateTime(fecha));
                    cn.AgregarParametros("tipo", tipo);
                    var sql = cn.EjecutarSpDataReader("sp_ADM_GetCotizacionHistorica");
                    dt.Load(sql);
                }
                else
                {
                    cn.AgregarParametros("tipo", tipo);
                    var sql = cn.EjecutarSpDataReader("sp_ADM_GetUltimaCotizacionHistorica");
                    dt.Load(sql);

                }

                cn.Cerrar();

                if(tipo != "Moneda")
                    result += "<Quotes>";
                else
                    result += "<Monedas>";

                foreach (DataRow item in dt.Rows)
                {

                    if (tipo != "Moneda")
                        result += "<Stock>";
                    else
                        result += "<Moneda>";

                    result += "<Symbol>" + item["Codigo"] + "</Symbol>";
                    result += "<Price>" + item["Valor"] + "</Price>";
                    result += "<Currency>" + item["Moneda"] + "</Currency>";
                    result += "<ChangeDay>" + item["CambioDia"] + "</ChangeDay>";
                    result += "<ChangeMonth>" + item["CambioMes"] + "</ChangeMonth>";
                    result += "<ChangeYear>" + item["CambioAno"] + "</ChangeYear>";
                    result += "<TIR>" + item["TIR"] + "</TIR>";

                    if (tipo != "Moneda")
                        result += "</Stock>";
                    else
                        result += "</Moneda>";
                }

                if (tipo != "Moneda")
                    result += "</Quotes>";
                else
                    result += "</Monedas>";

                xmlDoc.LoadXml(result.Replace("&", "&amp;"));

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return Newtonsoft.Json.JsonConvert.SerializeXmlNode(xmlDoc);
        }



        public static double ComputeIRR(List<double> cf, int numOfFlows)
        {

            int i = 0, j = 0;
            double old = 0.00;
            double newValue = 0.00;
            double oldguessRate = 0.01;
            double newguessRate = 0.01;
            double guessRate = 0.01;
            double lowGuessRate = 0.01;
            double highGuessRate = 0.5;
            double npv = 0.0;
            double denom = 0.0;

            for (i = 0; i < 1000; i++)
            {
                npv = 0.00;
                for (j = 0; j < numOfFlows; j++)
                {
                    denom = Math.Pow((1 + guessRate), j);
                    npv = npv + (cf[j] / denom);
                }

                /* Stop checking once the required precision is achieved */
                if ((npv > 0) && (npv < 0.00000001))
                    break;
                if (old == 0)
                {
                    old = npv;
                }
                else
                {
                    old = newValue;
                    newValue = npv;
                }
                if (i > 0)
                {
                    if (old < newValue)
                    {
                        if (old < 0 && newValue < 0)
                            highGuessRate = newguessRate;
                        else
                            lowGuessRate = newguessRate;
                    }
                    else
                    {
                        if (old > 0 && newValue > 0)
                            lowGuessRate = newguessRate;
                        else
                            highGuessRate = newguessRate;
                    }
                }
                oldguessRate = guessRate;
                guessRate = (lowGuessRate + highGuessRate) / 2;
                newguessRate = guessRate;
            }
            return guessRate;
        }


    }


    public class GoogleResponse
    {
        public string Kind { get; set; }
        public string Id { get; set; }
        public string LongUrl { get; set; }
    }

    public class CashFlow
    {

        public CashFlow(double amount, DateTime date) { Amount = amount; Date = date; }

        public readonly double Amount;
        public readonly DateTime Date;
    }

    public struct Pair<T, Z>
    {

        public Pair(T first, Z second) { First = first; Second = second; }

        public readonly T First;

        public readonly Z Second;

    }

    public struct AlgorithmResult<TKindOfResult, TValue>
    {

        public AlgorithmResult(TKindOfResult kind, TValue value)
        {

            Kind = kind;
            Value = value;
        }

        public readonly TKindOfResult Kind;
        public readonly TValue Value;
    }

    public enum ApproximateResultKind
    {
        ApproximateSolution,
        ExactSolution,
        NoSolutionWithinTolerance
    }

    public static class Algorithms
    {

        internal static double CalculateXNPV(IEnumerable<CashFlow> cfs, double r)
        {

            if (r <= -1)
                r = -0.99999999; // Very funky ... Better check what an IRR <= -100% means

            return (from cf in cfs
                    let startDate = cfs.OrderBy(cf1 => cf1.Date).First().Date
                    select cf.Amount / Convert.ToDouble(Math.Pow(1 + r, (cf.Date - startDate).Days / 365.0))).Sum();
        }

        internal static Pair<double, double> FindBrackets(Func<IEnumerable<CashFlow>, double, double> func, IEnumerable<CashFlow> cfs)
        {

            // Abracadabra magic numbers ...
            const int maxIter = 100;
            const double bracketStep = 0.5;
            const double guess = 0.1;

            double leftBracket = guess - bracketStep;
            double rightBracket = guess + bracketStep;
            var iter = 0;

            while (func(cfs, leftBracket) * func(cfs, rightBracket) > 0 && iter++ < maxIter)
            {

                leftBracket -= bracketStep;
                rightBracket += bracketStep;
            }

            if (iter >= maxIter)
                return new Pair<double, double>(0, 0);

            return new Pair<double, double>(leftBracket, rightBracket);
        }

        // From "Applied Numerical Analyis" by Gerald
        internal static AlgorithmResult<ApproximateResultKind, double> Bisection(Func<double, double> func, Pair<double, double> brackets, double tol, int maxIters)
        {

            int iter = 1;

            double f3 = 0;
            double x3 = 0;
            double x1 = brackets.First;
            double x2 = brackets.Second;

            do
            {
                var f1 = func(x1);
                var f2 = func(x2);

                if (f1 == 0 && f2 == 0)
                    return new AlgorithmResult<ApproximateResultKind, double>(ApproximateResultKind.NoSolutionWithinTolerance, x1);

                if (f1 * f2 > 0)
                    throw new ArgumentException("x1 x2 values don't bracket a root");

                x3 = (x1 + x2) / 2;
                f3 = func(x3);

                if (f3 * f1 < 0)
                    x2 = x3;
                else
                    x1 = x3;

                iter++;

            } while (Math.Abs(x1 - x2) / 2 > tol && f3 != 0 && iter < maxIters);

            if (f3 == 0)
                return new AlgorithmResult<ApproximateResultKind, double>(ApproximateResultKind.ExactSolution, x3);

            if (Math.Abs(x1 - x2) / 2 < tol)
                return new AlgorithmResult<ApproximateResultKind, double>(ApproximateResultKind.ApproximateSolution, x3);

            if (iter > maxIters)
                return new AlgorithmResult<ApproximateResultKind, double>(ApproximateResultKind.NoSolutionWithinTolerance, x3);

            throw new Exception("It should never get here");
        }

        public static AlgorithmResult<ApproximateResultKind, double> CalculateXIRR(IEnumerable<CashFlow> cfs, double tolerance, int maxIters)
        {

            var brackets = FindBrackets(CalculateXNPV, cfs);

            if (brackets.First == brackets.Second)
                return new AlgorithmResult<ApproximateResultKind, double>(ApproximateResultKind.NoSolutionWithinTolerance, brackets.First);

            return Bisection(r => CalculateXNPV(cfs, r), brackets, tolerance, maxIters);
        }
    }

    public class DataTooltip
    {        
        public string Codigo { get; set; }
        public string Nombre { get; set; }
        public string Descripcion { get; set; }
        public string UrlImagen { get; set; }
        public string DireccionWeb { get; set; }
    
     }

}


